Procedure with Cursor (case when) for send email-CodePudding

I want to write a stored procedure that will send a message to everyone based on case when, if the date in the table has passed.

I am trying to use a cursor but it take so long when executing the query.

This is my code:

DECLARE @email VARCHAR(50)
DECLARE @full_name VARCHAR(50)

DECLARE fungsi_cursor CURSOR FOR 
    SELECT
        CASE
            WHEN TP.analisis_deadline <= GETDATE() 
                 AND TP.status = 3 
                THEN (CASE
            WHEN TP.pic_pe = TU.full_name THEN TU.email
        END
)
END AS EMAIL,
CASE
    WHEN TP.analisis_deadline <= GETDATE() AND TP.status = 3 THEN 
(       
        CASE
            WHEN TP.pic_pe = TU.full_name THEN TU.full_name 
        END
)
END AS NAMA
FROM tbl_project TP
INNER JOIN tbl_user_pe TU ON TU.full_name = TP.pic_pe

OPEN fungsi_cursor
FETCH NEXT FROM fungsi_cursor INTO @full_name, @email

WHILE @@FETCH_STATUS = 0  
BEGIN

    DECLARE @subject varchar(30), @body varchar(250)

    SET @subject = 'Notification you are late in sending analysis'

    SET @body = '<table>
                     Hi ' @full_name  ', you are late in sending analysis, please update your analysis data immediately, to deactivate this email.
                </table>'

------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YHA SMTP',
        @recipients = @email,
        @copy_recipients='',
        @blind_copy_recipients ='',
        @subject = '',
        @body = @body,
        @body_format = 'HTML'
------------------------------------------

Please help me with it

CodePudding user response：

Your cursor is iterating over all rows.

You are populating your name and email address variables based on a case expression applied to every row, but then still attempting to send an email regardless.

Your case expression logic should be used as a where clause so you only iterate over the rows that qualify for having an email sent.

Of course, the more appropriate solution would be to pass the list of addresses to an application dedicated to sending emails and properly handling responses such as receipts and failures.

CodePudding user response：

first, thanks for all advice and suggestion, i solve my problem for now, i have a home work for using more dedicate email sending service

this my new code

AS
DECLARE @email VARCHAR(50), @full_name VARCHAR(50), @nama_project VARCHAR(150)

DECLARE fungsi_cursor CURSOR FOR 
    SELECT
        CASE
            WHEN TP.analisis_deadline <= GETDATE() 
                 AND TP.status = 3 
                THEN (CASE
            WHEN TP.pic_pe = TU.full_name THEN TU.email
        END
)
END AS EMAIL,
CASE
    WHEN TP.analisis_deadline <= GETDATE() AND TP.status = 3 THEN 
(       
        CASE
            WHEN TP.pic_pe = TU.full_name THEN TU.full_name 
        END
)
END AS NAMA,
TP.nama_project as nama_project
FROM tbl_project TP
INNER JOIN tbl_user_pe TU ON TU.full_name = TP.pic_pe
where TP.analisis_deadline <= GETDATE() AND TP.status = 3

OPEN fungsi_cursor
FETCH NEXT FROM fungsi_cursor INTO @email, @full_name, @nama_project

WHILE @@FETCH_STATUS = 0  
BEGIN

    DECLARE @subject varchar(30), @body varchar(250)

    SET @subject = 'Notification you are late in sending analysis'

    SET @body = '<table>
                     Hi ' @full_name  ', you are late in sending analysis for project '  @nama_project   ', please update your analysis data immediately, to deactivate this email.
                </table>'

------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YHA SMTP',
        @recipients = @email,
        @copy_recipients='',
        @blind_copy_recipients ='',
        @subject = '',
        @body = @body,
        @body_format = 'HTML'
------------------------------------------
FETCH NEXT FROM fungsi_cursor INTO @email, @full_name, @nama_project

END

CLOSE fungsi_cursor

DEALLOCATE fungsi_cursor