USP_CONSTITUENTRECOGNITION_DELETE

                CREATE procedure dbo.USP_CONSTITUENTRECOGNITION_DELETE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as begin

                    /*    
                    Sets Context Info to the CHANGEAGENTID
                    Deletes a row in the CONSTITUENTRECOGNITION table with the given ID
                    Resets Context info to the previous value

                    The Context Info will be used by delete triggers to log the AUDITCHANGEAGENTID 
                    as the supplied @CHANGEAGENTID rather than the default change agent id.
                    */

                    set nocount on;

                    declare @e int;
                    declare @contextCache varbinary(128);

                    /* cache current context information */
                    set @contextCache = CONTEXT_INFO();

                    /* set CONTEXT_INFO to @CHANGEAGENTID */
                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID

                    declare @RECOGNITIONPROGRAMID uniqueidentifier;
                    declare @CONSTITUENTID uniqueidentifier;
                    select @RECOGNITIONPROGRAMID = RECOGNITIONPROGRAMID, 
                        @CONSTITUENTID = CONSTITUENTID
                    from dbo.CONSTITUENTRECOGNITION 
                    where ID = @ID;

          -- TY, bug 127195 - clean up constituentrecognitionrevenue records

          delete from dbo.CONSTITUENTRECOGNITIONREVENUE
          where RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
          and REVENUERECOGNITIONID in(select ID
                                      from dbo.REVENUERECOGNITION
                                      where CONSTITUENTID = @CONSTITUENTID);

                    delete from dbo.CONSTITUENTRECOGNITION 
                        where CONSTITUENTID=@CONSTITUENTID 
                            and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID;

                    /* reset CONTEXT_INFO to previous value */
                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache

                    select @e=@@error;

                    if @e<>0 return -456; --always return non-zero sp result if an error occurs


                    return 0;                    

                end