Re-Executing Stored Procedure Until All Records Processed

  • I'm a hack, but somehow find ways to make SQL Server do things.  I'm changing my email, and decided to use SQL Server as a tool to mail my new address.

    I have an identity field called "Seq" and a field called "FirstTime" (defaulted to 999).  So I search for the MAX(Seq) WHERE FirstTime = 999, and that is the record that is processed by p_SendEmail (p_SendEmail is looking for a FirstTime = 123).  After the email is sent, I change any record with a 123 FirstTime to 1.

    QUESTION:

    I don't understand loops or triggers.  How can I keep re-executing this stored procedure, until all my FirstTime fields are no longer 999 and set to 1.  Thanks.

     

    SELECT

    MAX(Seq) as seq

    INTO #TempMaxSeq

    FROM _EmailList

    WHERE FirstTime = 999

    UPDATE _EmailList

    SET FirstTime = 123

    FROM _EmailList A

    LEFT JOIN #TempMaxSeq B

    ON A.Seq = B.Seq

    WHERE  A.Seq = B.Seq

    DROP TABLE #TempMaxSeq

    EXEC p_SendEmail

    UPDATE _EmailList

    SET FirstTime = 1

    WHERE FirstTime = 123

  • Hi,

    I may have missed something, but I am wondering why you are using a temporary table rather than a variable to hold the maximum sequence value?

    You can use a WHILE loop to keep processing the _EmailList table until Max(Seq) WHERE FirstTime=999 returns a NULL.

    e.g.:

    DECLARE @intTempMaxSeq int

    SET @intTempMaxSeq = (SELECT MAX(Seq) FROM _EmailList WHERE FirstTime = 999)

    WHILE @intTempMaxSeq IS NOT NULL

    BEGIN

      UPDATE _EmailList SET FirstTime=123 WHERE Seq = @intTempMaxSeq

      EXEC p_SendEmail

      UPDATE _EmailList SET FirstTime=1 WHERE Seq = @intTempMaxSeq

      SET @intTempMaxSeq = (SELECT MAX(Seq) FROM _EmailList WHERE FirstTime = 999)

    END

    David

    If it ain't broke, don't fix it...

  • "why you are using a temporary table rather than a variable to hold the maximum sequence value"

     

    I'm a hack.  Will look at your code.  Never used a variable.  Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply