USP_APPUSERPASSWORDRESET_ADD

create procedure dbo.USP_APPUSERPASSWORDRESET_ADD(
    @APPUSERID uniqueidentifier,
    @TOKEN nvarchar(50),
    @CHANGEAGENTID uniqueidentifier)
as
begin

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    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;

    -- Delete all pending password reset requests for the app user.

    delete from dbo.APPUSERPASSWORDRESET where APPUSERID = @APPUSERID;

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

    -- Open the symmetric key for encryption

    exec dbo.USP_GET_KEY_ACCESS;

    begin try
        insert into dbo.APPUSERPASSWORDRESET (
            APPUSERID,
            TOKEN,
            TOKENINDEX,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED)
        values (
            @APPUSERID,
            EncryptByKey(Key_GUID('sym_BBInfinity'), @TOKEN),
            dbo.UFN_GET_MAC_FOR_TEXT(@TOKEN, 'dbo.APPUSERPASSWORDRESET'),
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            getdate(),
            getdate());

        close symmetric key sym_BBInfinity;    
    end try
    begin catch
        EXEC dbo.USP_RAISE_ERROR;

        -- Make sure we close the symmetric key

        close symmetric key sym_BBInfinity;

        return 1;    
    end catch

    return 0;
end