Send an email for every row in a table in SQL Server

Question

When trying to send an email per row using this code, I'm getting one email per relevant row. However, each email only contains the value of the first row:

    DECLARE @BCC  VARCHAR(MAX) = ''
DECLARE @TO  VARCHAR(300) = 'a@gmail.com'   
    DECLARE @CC  VARCHAR(300) = ''    
    DECLARE @Sender  VARCHAR ( 300 ) = (SELECT ParameterValue FROM EmailParameters (NOLOCK) WHERE ParameterLabel = 'Sender')
    DECLARE @Subject  VARCHAR(150) = (Select [Subject] FROM [EmailTemplates]  WHERE EmailTemplateID = 10)
    DECLARE @Body  NVARCHAR(MAX) = (Select EmailTemplate FROM [EmailTemplates]  WHERE EmailTemplateID = 10) 
    DECLARE @IP  VArchar(15) = (SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL) --Get IP Address
    DECLARE @Date  Datetime
    DECLARE @Database  sysname          
    DECLARE @RestoreResult  NVARCHAR (50) 
    DECLARE @DbccResult  NVARCHAR (50) 
    DECLARE @ID  INT
    SET @Date = getdate() 


                    Set @Body = REPLACE(@Body, '@IP', @IP)
                    Set @Subject = REPLACE(@Subject, '@Sender', @Sender)
                    Set @Subject = REPLACE(@Subject, '@Date', @Date)        

            DECLARE BackupCursor CURSOR FOR
        SELECT [Database], RestoreResult, DbccResult, ID
        FROM BackupTestResults_Alerting
        WHERE SentFlag = 0;

            -- Open cursor
    OPEN BackupCursor;

            -- Get first row
    FETCH NEXT FROM BackupCursor 

     INTO @Database, @RestoreResult, @DbccResult, @ID;

                    -- While there is data
    WHILE (@@fetch_status = 0)
    BEGIN
            Set @Body = REPLACE(@Body, '@Database', @Database)
                    Set @Body = REPLACE(@Body, '@RestoreResult', @RestoreResult)
                    Set @Body = REPLACE(@Body, '@DbccResult', @DbccResult) 
                

                            INSERT INTO [EmailQueue] 
                    SELECT 10,
                           @Body,
                           @To,
                           @Cc,
                           @Bcc,
                           @Subject,
                           0   
                           EXEC SDP.dbo.usp_Email_Send

    print @dbccResult
        -- Grab the next record
        FETCH NEXT FROM BackupCursor 
            INTO @Database, @RestoreResult, @DbccResult, @ID;
    END

    -- Close cursor
    CLOSE BackupCursor;

    -- Release memory
    DEALLOCATE BackupCursor;  

The Replace function is dynamically replacing variables that appear in the html body of each email. The print command correctly displays each row that I'd like to send an email for. However, only the values for the first row are being displayed in each email.

How can I ensure that the correct values for each row get passed to each email?

EDIT: Thank you all for the informative replies. I'll include more information on my overall goal.

I have automated backup testing and would like to receive an email alert whenever a backup test fails. Currently, I can achieve this by logging the backup testing results to a results table, copying any failed results to an alerting table, then having a job read from the alerting table and send me an email for any failures. The main limitation is this job can only catch one row at a time, so it needs to be run multiple times in case there is more than one failed result.

I would like to find a more elegant solution where the email alert job can be merged into the backup testing job as a final step.

Answer 1

If I've understood correctly then as soon as you run your first replace, you've removed that parameter from @Body and all future REPLACE statements will fail. Try changing your cursor to this:

WHILE (@@fetch_status = 0)
BEGIN
DECLARE @EmailBody NVARCHAR(MAX) = @Body
        Set @EmailBody = REPLACE(@emailBody, '@Database', @Database)
                Set @EmailBody = REPLACE(@EmailBody, '@RestoreResult', @RestoreResult)
                Set @EmailBody = REPLACE(@EmailBody, '@DbccResult', @DbccResult) 
            

                        INSERT INTO [EmailQueue] 
                SELECT 10,
                       @EmailBody,
                       @To,
                       @Cc,
                       @Bcc,
                       @Subject,
                       0   
                       EXEC SDP.dbo.usp_Email_Send
                       
                       

print @dbccResult
    -- Grab the next record
    FETCH NEXT FROM BackupCursor 
        INTO @Database, @RestoreResult, @DbccResult, @ID;
END

It's not pretty but it should work