0

I have created a stored procedure as below, it will run as a scheduled job. The work of the stored procedure is to check through an attendance table (which I have omitted in the query sample) and sends email alerts to employees and their managers regarding absence.

The query returns multiple rows.

The procedure works fine without any problem but sends email only once, I believe that I have to loop through the retrieved data. I tried using cursors and temporary tables but getting lots of errors and it was getting messy.

So I am sharing herewith the working stored procedure and if somebody can help me out to loop it easily and effectively, I would appreciate it.

Thanks.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ABC]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PositionCode NVARCHAR(9), @FullNameE NVARCHAR(50),
            @Email NVARCHAR(100), @OutputSubject NVARCHAR(500), 
            @OutPutBody NVARCHAR(MAX), @OutPutEmail NVARCHAR(550), 
            @OutPutDateTime NVARCHAR(100)
    DECLARE @CRLF char(2)

    SELECT @CRLF = CHAR(13) + CHAR(10)

    SELECT  
        @rno = ROW_NUMBER() OVER (ORDER BY ED.POsitionCode),   
        @FullNameE = EP.FullNameE, @Email = ED.Email
    FROM 
        Query

    BEGIN
        SET @OutputSubject =  'Absent Employee - ' + @PositionCode + ' - ' + @FullNameE

        SET @OutPutDateTime = (Select cast(GetDate() as datetime))
        SET @OutPutBody = '<html><body>Dear Mr/Ms. ' + @FullNameE +
      ', <br><br> Please note that you were <b><font color="red">absent</font></b> during the <b><font color="red">last working day </font></b>: <br><br>Employee Code - '
      + @PositionCode + ' - ' + @FullNameE + ' </body></html>'


        SET @OutPutEmail =  @Email + '; abc@abc.com; xyz@xyz.com'

        EXEC msdb.dbo.sp_send_dbmail
                  @recipients =  @OutPutEmail,
                  @profile_name = 'Alert',
                  @subject = @OutputSubject,
                  @body_format ='HTML',
                  @body =  @OutPutBody;
    END
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3625561
  • 305
  • 5
  • 25
  • If you need to send an individual e-mail for each row of the query result I'd use a simple cursor to step through the result set. – Vladimir Baranov Jun 22 '16 at 07:39
  • yes, i would like to send individual emails per row. can you give an example? Thanks. – user3625561 Jun 22 '16 at 07:39
  • search for "sql server cursor example": http://stackoverflow.com/questions/4974981/sql-server-cursor http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/ https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/ http://stevestedman.com/2013/04/t-sql-a-simple-example-using-a-cursor/ – Vladimir Baranov Jun 22 '16 at 07:44

0 Answers0