WaitForDelay - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WaitForDelay

Author

Topic

robayb
Starting Member

7 Posts

Posted - 2015-03-06 : 16:13:17

Can I get confirmation that this code is sound. I have never used the WAIT for DELAY term before and I'm not 100% sure this will run correctly.

So basically i'm doing two checks . One that the date exists... If this is FALSE then I wait 15 mins. If TRUE then 2nd check if data exists and if NOT then call stored Procedure and send an email, if 2nd check DOES exist then stop. Thank you in advance.

BEGIN --2
DECLARE @SQLQuery AS NVARCHAR(500)

SET @SQLQuery = (SELECT ASofDATE from VMOPS.dbo.tblCAInvSnapUpdated_ASOFDATE)

IF @SQLQuery = DateAdd(Day, Datediff(Day,0, CONVERT(DATETIME, getdate())), 0)--IF INV SNAPSHOT IS DONE FOR TODAY

BEGIN--3
-- IF SKUSALES_US UPDATE ALREADY HAPPENED
IF NOT EXISTS(SELECT [id]
FROM [CFT].[dbo].[CriticalTableUpdates]
where SPname = 'VMOPS.CFT_PM_SKUSALES_US'
and DATEDIFF(d,LastUpdate,getdate()) = 0
)
EXECUTE dbo.CFT_PM_SKUSALES_US
--Send email
DECLARE
@body nvarchar(MAX),
@toWho nvarchar(100)

SET @body = '<html><body>Hello,
This is confirmation that VMOPS.CFT_PM_SKUSALES_US has been updated today.</body></html>'

Set @toWho='xxxxx'

EXEC msdb.dbo.sp_send_dbmail
@recipients=@toWho,
@subject = 'CFT-SKUSALES_US UPDATED',
--@blind_copy_recipients=@BCC2,
@body = @body ,
@profile_name = 'VM Operations Public Profile',
@body_format = 'HTML' ;
END --3
ELSE --IF INV SNAPSHOT IS NOT DONE THEN WAIT 15 MINS
WAITFOR DELAY '00:15'; --This should loop back to IF @SQLQuery line

END --2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-06 : 16:46:36

quote:

WAITFOR DELAY '00:15'; --This should loop back to IF @SQLQuery line

Your code doesn't loop back at all. I don't see a loop or a GOTO to make it go back.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

robayb
Starting Member

7 Posts

Posted - 2015-03-06 : 19:36:52

quote:
Originally posted by tkizer

quote:

WAITFOR DELAY '00:15'; --This should loop back to IF @SQLQuery line

Your code doesn't loop back at all. I don't see a loop or a GOTO to make it go back.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Ahhh...Is this better? These flow commands are new to me.

StartProcess1:
BEGIN --2
DECLARE @SQLQuery AS NVARCHAR(500)

SET @SQLQuery = (SELECT ASofDATE from VMOPS.dbo.tblCAInvSnapUpdated_ASOFDATE)

IF @SQLQuery = DateAdd(Day, Datediff(Day,0, CONVERT(DATETIME, getdate())), 0)--IF INV SNAPSHOT IS DONE FOR TODAY

BEGIN--3
-- IF SKUSALES_US UPDATE ALREADY HAPPENED
IF NOT EXISTS(SELECT [id]
FROM [CFT].[dbo].[CriticalTableUpdates]
where SPname = 'VMOPS.CFT_PM_SKUSALES_US'
and DATEDIFF(d,LastUpdate,getdate()) = 0
)
EXECUTE dbo.CFT_PM_SKUSALES_US
--Send email
DECLARE
@body nvarchar(MAX),
@toWho nvarchar(100)

SET @body = '<html><body>Hello,
This is confirmation that VMOPS.CFT_PM_SKUSALES_US has been updated today.</body></html>'

Set @toWho='xxxx.com'

EXEC msdb.dbo.sp_send_dbmail
@recipients=@toWho,
@subject = 'CFT-SKUSALES_US UPDATED',
--@blind_copy_recipients=@BCC2,
@body = @body ,
@profile_name = 'VM Operations Public Profile',
@body_format = 'HTML' ;

GOTO endProcess;

END --3
ELSE --IF INV SNAPSHOT IS NOT DONE THEN WAIT 15 MINS
WAITFOR DELAY '00:15'; --This should loop back to IF @SQLQuery line
GOTO StartProcess1;
endProcess:
END --2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-06 : 19:42:21

I don't like the use of GOTO as it's considered bad code. I would use a WHILE loop instead. I only mentioned GOTO as your code had no way for it to go back up after the WAITFOR.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

robayb
Starting Member

7 Posts

Posted - 2015-03-06 : 19:45:55

quote:
Originally posted by tkizer

I don't like the use of GOTO as it's considered bad code. I would use a WHILE loop instead. I only mentioned GOTO as your code had no way for it to go back up after the WAITFOR.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Thats a bummer since it seems very powerful and easy to use..maybe thats what makes it dangerous or bad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-06 : 19:49:19

It's not dangerous. It's just ancient. I am surprised T-SQL still supports it or that the ANSI standard still has it. It's just not something people use these days and is considered bad programming.

A simple WHILE loop will achieve what you want. WHILE <some condition is true>, or even WHILE 1=1/BREAK.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources