SQL Server stored procedure running in infinite loop

Question

I am running a stored procedure which is running infinitely.

I have used a while loop that seems to be running without ever ending.

CREATE PROCEDURE ABC
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Id INT;
    DECLARE @iter INT = 1;
    DECLARE @iterMax INT;
    DECLARE @psubject VARCHAR(100);
    DECLARE @pbody NVARCHAR(MAX);
    DECLARE @pSendTo NVARCHAR(MAX);
    DECLARE @pProfile VARCHAR(MAX);

    IF OBJECT_ID('tempdB..#temp') IS NOT NULL
        DROP TABLE #temp;

    SET @pProfile = 'Test';

    IF ((SELECT COUNT(*)
         FROM [Table_A] R
         JOIN [Table_B] T ON R.Id = T.r_Id
         WHERE R.[Date] <= (DATEADD(DAY, -1, GETDATE()))
           AND T.[Sent_Flag] IS NULL) >= 1)
    BEGIN
        SELECT IDENTITY(int, 1, 1) AS RecId,
               [r_id] * 1 AS Id
        INTO #temp
        FROM [Table_A] R
        JOIN [Table_B] T ON R.Id = T.r_Id
        WHERE R.[Date] <= (DATEADD(DAY, -1, GETDATE()))
          AND T.[Sent_Flag] IS NULL;

        BEGIN
            SET @iterMax = (SELECT COUNT(*)FROM #temp);

            WHILE (@iter <= @iterMax)
            BEGIN
                SET @psubject = 'HIIII'; /*this is in HTML example */
                SET @pbody = 'You got one email';

                IF ((SELECT COUNT(*)
                     FROM [Table_B]
                     WHERE R_Id = (SELECT Id FROM #temp WHERE RecId = @iter)
                       AND [Mail1_Flag] = 'Y'
                       AND [Mail2_Flag] = 'Y') = 1)
                BEGIN
                    IF ((SELECT COUNT(*)
                         FROM [Table_A] R
                         JOIN [Table_B] T ON R.Id = T.r_Id
                         WHERE R_Id = (SELECT Id FROM #temp WHERE RecId = @iter)
                           AND R.[Date] <= (DATEADD(DAY, -1, GETDATE()))
                           AND T.[Sent_Flag] IS NULL) = 1)
                    BEGIN
                        SET @pSendTo = N'[email protected]';

                        EXEC msdb.dbo.sp_send_dbmail @profile_name = @pProfile,
                                                     @body = @pbody,
                                                     @subject = @psubject,
                                                     @recipients = @pSendTo,
                                                     @body_format = 'HTML';

                    END;

                    UPDATE [Table_B]
                    SET [Sent_Flag] = 'Y'
                    WHERE [Id] IN (SELECT Id FROM #temp WHERE RecId = @iter);
                END;
            END;

            SET @iter = @iter + 1;
        END;
    END;

    IF OBJECT_ID('tempd..#temp') IS NOT NULL
        DROP TABLE #temp;
END;

This program is checking that if date is more than 24 hours then it will send a mail correspondingly. I am able to trigger a mail. But I am getting multiple mails. Like the loop is running infinitely and getting same mail multiple times and the sent_Flag column is initial NULL and after a mail is sent it sholud update to 'Y' but it is also not updating to 'Y' after mail is triggered.

Please help to resolve this issue. Thank you

The fact that you are using a WHILE loop in the first place infers a design flaw. What are you trying to achieve with the SP? Also, I note that in your WHILE (@iter <= @iterMax) section you never change the value of @iter or @iterMax, so that WHILE will never be exitted. (You can see this quite easily now too, as I formatted and aligned your code for you.) — Thom A
– Thom A ♦, Commented Aug 12, 2019 at 16:00
Why not do the processing based on the value of Sent_Flag? — Brian
– Brian, Commented Aug 12, 2019 at 16:07
^ Instead of WHILE (@iter <= @iterMax) use WHILE EXISTS (SELECT 1 FROM #temp WHERE Sent_Flag IS NULL) — Aaron Bertrand
– Aaron Bertrand, Commented Aug 12, 2019 at 16:45
Honestly, this type of one-by-one looping is what cursors are made for. Your coding is much simpler to understand (but frowned upon by many) if you use a cursor. — Laughing Vergil
– Laughing Vergil, Commented Aug 12, 2019 at 16:51
But to be clear, a cursor is no better or worse than a while loop in terms of performance (it's a common misconception that while loops are "better"). — Aaron Bertrand
– Aaron Bertrand, Commented Aug 12, 2019 at 17:00

Tab Alleman · Accepted Answer · 2019-08-13 13:43:16Z

1

You are not incrementing the counter inside the loop:

                UPDATE [Table_B]
                SET [Sent_Flag] = 'Y'
                WHERE [Id] IN (SELECT Id FROM #temp WHERE RecId = @iter);
            END;   --this is the END of the first IF BEGIN
        END;  --this is the END of the WHILE BEGIN

        SET @iter = @iter + 1;   --and here you update the counter, which will never be reached

If you don't increment the counter inside the loop, the loop will run infinitely, since the loop condition will always be true.

edited Aug 13, 2019 at 13:43

answered Aug 12, 2019 at 18:18

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server stored procedure running in infinite loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related