USP_EMAIL_UPDATE_SENDGENERALPUPROSEREQUEST

CREATE procedure dbo.USP_EMAIL_UPDATE_SENDGENERALPUPROSEREQUEST
(
    @EMAILID int,
    @EMAILJOBID int,
    @USERID int,
    @EMAILADDRESS nvarchar(255),
    @DISPLAYNAME nvarchar(255),
    @MERGEID uniqueidentifier
)
as
begin

    declare @NOW datetime
    set @NOW = GETDATE()

    declare @EMAILRECIPIENTID int
    --insert into Email_Recipient

    INSERT INTO Email_Recipient
    (
        [EmailID],
        [USerID],
        [AddressBookID],
        [DisplayName],
        [EmailAddress],
        [DataSourceID],
        [BackOfficeSystemPeopleID]
    )
    VALUES
    (
        @EMAILID,
        @USERID,
        0,
        @DISPLAYNAME,
        @EMAILADDRESS,
        NULL,
        NULL
    )
    set @EMAILRECIPIENTID=@@IDENTITY


    --insert into EmailJob_Recipient

    declare @ERID int
    INSERT INTO EmailJob_Recipient
    (
        [EMAILID],
        [EMAILJOBID],
        [USERID],
        [EMAILADDRESS],
        [MESSAGEDATE],
        [SENTDATE],
        [UPDATEDATE],
        [SENT],
        [OPENED],
        [OPENEDDATE],
        [DSNED],
        [RECENTDSNDATE],
        [RECENTDSNSIGNATUREID],
        [RECENTDSNCATEGORY],
        [STATUSREPORT],
        [COMPLETED],
        [ADDRESSBOOKID],
        [EMAILRECIPIENTID],
        [BACKOFFICESYSTEMPEOPLEID],
        [MERGEID]
    )
    VALUES
    (
        @EMAILID,
        @EMAILJOBID,
        @USERID,
        @EMAILADDRESS,
        @NOW,
        @NOW,
        @NOW,
        0,
        0,
        NULL,
        0,
        NULL,
        NULL,
        NULL,
        NULL,
        0,
        0,
        @EMAILRECIPIENTID,
        NULL,
        @MERGEID
    )
    set @ERID= @@IDENTITY

    --update EMAILJOB's status to 'processed'

    update EMAILJOB set Status=12 where ID=@EMAILJOBID

    return @ERID
end