SOLVED

Modify send mail procedure

Copper Contributor

Hi all!

My local database moved to azure, and now I need to adjust my procedure to insert data into a table, instead of execute msdb.dbo.sp_send_dbmail. This is the procedure:

PROCEDURE [dbo].[SendMailMessage]
(
	@AMEI INT,
	@DeclarationID UNIQUEIDENTIFIER,
	@Action NVARCHAR(50)
)
WITH EXECUTE AS CALLER
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Get the mail message(s)
	DECLARE
		@Role NVARCHAR(50),
		@MailSubject NVARCHAR(255),
		@MailMessage NVARCHAR(MAX),
		@LogMessage NVARCHAR(MAX),
		@OwnerAMEI INT,
		@OwnerFullName NVARCHAR(255),
		@RecipientAMEI INT,
		@RecipientFullName NVARCHAR(255),
		@RecipientEmail NVARCHAR(255),
		@RecipientLanguageCode CHAR(2),
		@AssessmentYear INT

	-- An item in the outbox can have multiple receivers
	DECLARE cur_messages CURSOR FOR
		SELECT mm.[Role], ISNULL(mm.Subject, mm.Subject), ISNULL(mm.Message, mm.Message)
		FROM WF_MailMessages mm
		WHERE mm.Action = @Action
			AND mm.LanguageCode = 'EN'
	
	OPEN cur_messages
	FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage
	
	WHILE @@FETCH_STATUS = 0 BEGIN	

		-- Get assessment year from declaration
		SELECT @AssessmentYear = Assessment
		FROM Declarations
			WHERE ID = @DeclarationID

		-- Get recipient's information
		SELECT @RecipientAMEI = AMEI
		FROM WF_DeclarationActors
		WHERE DeclarationID = @DeclarationID
			AND [Role] = @Role

		SELECT @RecipientFullName = e.FullName, @RecipientEmail = e.Email, @RecipientLanguageCode = e.LanguageCode
		FROM Employees e
		WHERE e.AMEI = @RecipientAMEI
			AND e.Assessment = @AssessmentYear

		-- Get owner information
		SELECT @OwnerAMEI = AMEI
		FROM WF_DeclarationActors
		WHERE DeclarationID = @DeclarationID
			AND [Role] = 'OWNER'

		SELECT @OwnerFullName = e.FullName
		FROM Employees e
		WHERE e.AMEI = @OwnerAMEI
			AND e.Assessment = @AssessmentYear

		-- Check recipient has a different language then EN
		If (@RecipientLanguageCode is not null AND @RecipientLanguageCode <> 'EN')
		BEGIN
			DECLARE @NewSubject NVARCHAR(255),
			@NewMessage NVARCHAR(MAX)

			SELECT @NewSubject = ISNULL(mm.Subject, mm.Subject), @NewMessage = ISNULL(mm.Message, mm.Message)
			FROM WF_MailMessages mm
			WHERE mm.Action = @Action
				AND mm.LanguageCode = @RecipientLanguageCode

			IF @NewSubject is not null SET @MailSubject = @NewSubject
			IF @NewMessage is not null SET @MailMessage = @NewMessage
		END

		-- Substitute placeholders
		SET @MailSubject = REPLACE(@MailSubject, '#OWNER#', @OwnerFullName)
		SET @MailMessage = REPLACE(@MailMessage, '#OWNER#', @OwnerFullName)
		SET @MailMessage = REPLACE(@MailMessage, '#N+1#', @RecipientFullName)
		SET @MailMessage = REPLACE(@MailMessage, '#MANAGEMENT#', @RecipientFullName)
		SET @MailMessage = REPLACE(@MailMessage, '#COMP_OFF#', @RecipientFullName)
		SET @MailMessage = REPLACE(@MailMessage, '#URL#', 'mysite')

			EXEC msdb.dbo.sp_send_dbmail 
				@from_address = 'email address removed for privacy reasons', 
				@recipients = @RecipientEmail, -- 'email address removed for privacy reasons',  @RecipientEmail
				--@blind_copy_recipients = 'email address removed for privacy reasons',
				@subject = @MailSubject, 
				@body = @MailMessage, 
				@body_format = 'HTML',
				@profile_name = 'dbmail'

	BEGIN CATCH
		SET @LogMessage = 'Error occured during SendNotification:' + CONVERT(VARCHAR, @AMEI) + ', ' + CONVERT(VARCHAR(150), @RecipientEmail) 
		SET @LogMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)
			
		EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI
	END CATCH

		
		SET @LogMessage = 'SendMailMessage(' + @Action + ', ' + @RecipientLanguageCode + '): ' + @MailMessage
		EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI
		
		FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage
	END
	
	CLOSE cur_messages
	DEALLOCATE cur_messages	
END

How can I modify this code do insert data into a table instead send mail ? 

1 Reply
best response confirmed by douglasfilipe (Copper Contributor)
Solution

@douglasfilipe you can create a table to store the mail information and then replace the sp_send_dbmail with an INSERT INTO statement using this modified procedure:

CREATE TABLE dbo.MailLog (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RecipientEmail NVARCHAR(255),
    Subject NVARCHAR(255),
    Message NVARCHAR(MAX),
    SentDateTime DATETIME DEFAULT GETDATE()
);

-- Modify the procedure
ALTER PROCEDURE [dbo].[SendMailMessage]
(
    @AMEI INT,
    @DeclarationID UNIQUEIDENTIFIER,
    @Action NVARCHAR(50)
)
WITH EXECUTE AS CALLER
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the mail message(s)
    DECLARE
        @Role NVARCHAR(50),
        @MailSubject NVARCHAR(255),
        @MailMessage NVARCHAR(MAX),
        @LogMessage NVARCHAR(MAX),
        @OwnerAMEI INT,
        @OwnerFullName NVARCHAR(255),
        @RecipientAMEI INT,
        @RecipientFullName NVARCHAR(255),
        @RecipientEmail NVARCHAR(255),
        @RecipientLanguageCode CHAR(2),
        @AssessmentYear INT

    -- An item in the outbox can have multiple receivers
    DECLARE cur_messages CURSOR FOR
        SELECT mm.[Role], ISNULL(mm.Subject, mm.Subject), ISNULL(mm.Message, mm.Message)
        FROM WF_MailMessages mm
        WHERE mm.Action = @Action
            AND mm.LanguageCode = 'EN'

    OPEN cur_messages
    FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage

    WHILE @@FETCH_STATUS = 0 BEGIN

        -- Get assessment year from declaration
        SELECT @AssessmentYear = Assessment
        FROM Declarations
            WHERE ID = @DeclarationID

        -- Get recipient's information
        SELECT @RecipientAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = @Role

        SELECT @RecipientFullName = e.FullName, @RecipientEmail = e.Email, @RecipientLanguageCode = e.LanguageCode
        FROM Employees e
        WHERE e.AMEI = @RecipientAMEI
            AND e.Assessment = @AssessmentYear

        -- Get owner information
        SELECT @OwnerAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = 'OWNER'

        SELECT @OwnerFullName = e.FullName
        FROM Employees e
        WHERE e.AMEI = @OwnerAMEI
            AND e.Assessment = @AssessmentYear

        -- Check recipient has a different language than EN
        If (@RecipientLanguageCode is not null AND @RecipientLanguageCode <> 'EN')
        BEGIN
            DECLARE @NewSubject NVARCHAR(255),
            @NewMessage NVARCHAR(MAX)

            SELECT @NewSubject = ISNULL(mm.Subject, mm.Subject), @NewMessage = ISNULL(mm.Message, mm.Message)
            FROM WF_MailMessages mm
            WHERE mm.Action = @Action
                AND mm.LanguageCode = @RecipientLanguageCode

            IF @NewSubject is not null SET @MailSubject = @NewSubject
            IF @NewMessage is not null SET @MailMessage = @NewMessage
        END

        -- Substitute placeholders
        SET @MailSubject = REPLACE(@MailSubject, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#N+1#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#MANAGEMENT#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#COMP_OFF#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#URL#', 'mysite')

        -- Instead of sending mail, insert into the MailLog table
        INSERT INTO dbo.MailLog (RecipientEmail, Subject, Message)
        VALUES (@RecipientEmail, @MailSubject, @MailMessage);

        BEGIN CATCH
            SET @LogMessage = 'Error occurred during SendNotification:' + CONVERT(VARCHAR, @AMEI) + ', ' + CONVERT(VARCHAR(150), @RecipientEmail)
            SET @LogMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)

            EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI
        END CATCH

        SET @LogMessage = 'SendMailMessage(' + @Action + ', ' + @RecipientLanguageCode + '): ' + @MailMessage
        EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI

        FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage
    END

    CLOSE cur_messages
    DEALLOCATE cur_messages
END

 

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
 
Best Regards,
Dallas.
1 best response

Accepted Solutions
best response confirmed by douglasfilipe (Copper Contributor)
Solution

@douglasfilipe you can create a table to store the mail information and then replace the sp_send_dbmail with an INSERT INTO statement using this modified procedure:

CREATE TABLE dbo.MailLog (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RecipientEmail NVARCHAR(255),
    Subject NVARCHAR(255),
    Message NVARCHAR(MAX),
    SentDateTime DATETIME DEFAULT GETDATE()
);

-- Modify the procedure
ALTER PROCEDURE [dbo].[SendMailMessage]
(
    @AMEI INT,
    @DeclarationID UNIQUEIDENTIFIER,
    @Action NVARCHAR(50)
)
WITH EXECUTE AS CALLER
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the mail message(s)
    DECLARE
        @Role NVARCHAR(50),
        @MailSubject NVARCHAR(255),
        @MailMessage NVARCHAR(MAX),
        @LogMessage NVARCHAR(MAX),
        @OwnerAMEI INT,
        @OwnerFullName NVARCHAR(255),
        @RecipientAMEI INT,
        @RecipientFullName NVARCHAR(255),
        @RecipientEmail NVARCHAR(255),
        @RecipientLanguageCode CHAR(2),
        @AssessmentYear INT

    -- An item in the outbox can have multiple receivers
    DECLARE cur_messages CURSOR FOR
        SELECT mm.[Role], ISNULL(mm.Subject, mm.Subject), ISNULL(mm.Message, mm.Message)
        FROM WF_MailMessages mm
        WHERE mm.Action = @Action
            AND mm.LanguageCode = 'EN'

    OPEN cur_messages
    FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage

    WHILE @@FETCH_STATUS = 0 BEGIN

        -- Get assessment year from declaration
        SELECT @AssessmentYear = Assessment
        FROM Declarations
            WHERE ID = @DeclarationID

        -- Get recipient's information
        SELECT @RecipientAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = @Role

        SELECT @RecipientFullName = e.FullName, @RecipientEmail = e.Email, @RecipientLanguageCode = e.LanguageCode
        FROM Employees e
        WHERE e.AMEI = @RecipientAMEI
            AND e.Assessment = @AssessmentYear

        -- Get owner information
        SELECT @OwnerAMEI = AMEI
        FROM WF_DeclarationActors
        WHERE DeclarationID = @DeclarationID
            AND [Role] = 'OWNER'

        SELECT @OwnerFullName = e.FullName
        FROM Employees e
        WHERE e.AMEI = @OwnerAMEI
            AND e.Assessment = @AssessmentYear

        -- Check recipient has a different language than EN
        If (@RecipientLanguageCode is not null AND @RecipientLanguageCode <> 'EN')
        BEGIN
            DECLARE @NewSubject NVARCHAR(255),
            @NewMessage NVARCHAR(MAX)

            SELECT @NewSubject = ISNULL(mm.Subject, mm.Subject), @NewMessage = ISNULL(mm.Message, mm.Message)
            FROM WF_MailMessages mm
            WHERE mm.Action = @Action
                AND mm.LanguageCode = @RecipientLanguageCode

            IF @NewSubject is not null SET @MailSubject = @NewSubject
            IF @NewMessage is not null SET @MailMessage = @NewMessage
        END

        -- Substitute placeholders
        SET @MailSubject = REPLACE(@MailSubject, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#OWNER#', @OwnerFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#N+1#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#MANAGEMENT#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#COMP_OFF#', @RecipientFullName)
        SET @MailMessage = REPLACE(@MailMessage, '#URL#', 'mysite')

        -- Instead of sending mail, insert into the MailLog table
        INSERT INTO dbo.MailLog (RecipientEmail, Subject, Message)
        VALUES (@RecipientEmail, @MailSubject, @MailMessage);

        BEGIN CATCH
            SET @LogMessage = 'Error occurred during SendNotification:' + CONVERT(VARCHAR, @AMEI) + ', ' + CONVERT(VARCHAR(150), @RecipientEmail)
            SET @LogMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)

            EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI
        END CATCH

        SET @LogMessage = 'SendMailMessage(' + @Action + ', ' + @RecipientLanguageCode + '): ' + @MailMessage
        EXEC InsertLog2 @AMEI, @LogMessage , NULL, @DeclarationID, @OwnerAMEI

        FETCH NEXT FROM cur_messages INTO @Role, @MailSubject, @MailMessage
    END

    CLOSE cur_messages
    DEALLOCATE cur_messages
END

 

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
 
Best Regards,
Dallas.

View solution in original post