USP_PROSPECTRESEARCHREQUEST_DELETE

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


    declare @contextCache varbinary(128);
    --cache current context information

    set @contextCache = CONTEXT_INFO();
    --set CONTEXT_INFO to @CHANGEAGENTID

    set CONTEXT_INFO @CHANGEAGENTID;

    --check deletion rules, if any

    if (select STATUSCODE from dbo.PROSPECTRESEARCHREQUEST where ID = @ID) not in(0, 1, 5, 6) begin
        raiserror('BBERR_PROSPECTRESEARCHREQUEST_BADDELETESTATE', 13, 1);
        return 1;
    end

    if exists(select ID from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where STATUSCODE not in(0, 1, 5, 6) and PROSPECTRESEARCHREQUESTID = @ID) begin
        raiserror('BBERR_PROSPECTRESEARCHREQUEST_BADCHILDDELETESTATE', 13, 1);
        return 1;
    end

    delete from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT
    where
        PROSPECTRESEARCHREQUESTID = @ID

    -- use the system generated delete routine to allow proper recording of the deleting agent

    exec USP_PROSPECTRESEARCHREQUEST_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    return 0;

end