January 21, 2006 at 3:22 am
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
January 22, 2006 at 8:22 am
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...
January 22, 2006 at 5:56 pm
"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