A WAITFOR UNTIL implentation for T-SQL
You've probably encountered situations where you needed to pause the execution of a stored procedure until some condition became true or needed to run some code when a condition became true. If the condition is time-based, WAITFOR TIME | DELAY can come in handy, but what if it's a logical condition? What if you needed to wait until an order was processed? What if you needed to wait until a given number of rows showed up in a staging table? What if you wanted to run some special code when blocking showed up on your system?
In SS2K, SQL Agent provides some nice polling-based tools for doing something like this, and we've got Service Broker coming in Yukon, but you may need something that's T-SQL-based and that works on SQL Server 7.0/2000 (as well as Yukon). If so, here's a proc for you:
CREATE PROC dbo.sp_wait_until @WaitCondition nvarchar(4000),
@WaitMessage nvarchar(4000)='Wait condition met.',
@PollingInterval char(8)='00:00:05',
@PollingMessage nvarchar(4000)=NULL
AS
DECLARE @Stop int
SET @Stop=0
SET @WaitCondition=N'IF ('+@WaitCondition+N') RAISERROR('''+@WaitMessage+N''',11,1)'
WHILE (@Stop=0) BEGIN
EXEC @Stop=sp_executesql @WaitCondition
IF (@Stop=0) AND (@PollingMessage IS NOT NULL)
PRINT @PollingMessage
WAITFOR DELAY @PollingInterval
END
The proc takes four parameters -- the wait condition (the condition to wait on to become true), an optional wait message, the polling interval (defaults to 5 seconds), and an optional polling message. @WaitCondition is the only required parameter and consists of the T-SQL logical expression that you want to wait on to become true. Here's an example that waits for a given table to show up in tempdb:
sp_wait_until 'object_id(''tempdb.dbo.foo'') IS NOT NULL', @PollingMessage='Checking...'
Here's another that waits for blocking to occur:
sp_wait_until 'EXISTS(select * from master.dbo.sysprocesses where blocked<>0)', @WaitMessage='Blocking detected!', @PollingMessage='Checking...'
The proc uses sp_executesql and its ability to return an error code as a result code to pull this off. Have a look at the way the proc wraps @WaitCondition in a simple IF that calls RAISERROR() if the condition is true. Other than that, the only other noteworthy feature of the proc is the use of WAITFOR DELAY to wait on the condition in an efficient manner.
I'm sure you could think of all sorts of nifty enhancements to this code. For example, you might add a timeout parameter to the proc and vary the return code from the proc based on whether it timed out. You might pass in code to run when the condition becomes true. And you might use RAISERROR()...NOWAIT to display the polling message so that it goes to the client immediately rather than waiting on the network buffer to fill. Feel free to enhance it as you see fit. I'd be interested to hear about any particularly novel ideas you come up with.
This is a scaled down version of the sp_proc_runner code in my book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML. See the Administrative Stored Procedures chapter in that book for more info.
Comments
- Anonymous
July 18, 2005
This article, http://www.sqlservercentral.com/columnists/kThaker/optimizingcursorperformance.asp supposedly by Kalpesh Thaker, appears to be lifted almost word-for-word from your book The Guru's Guide to SQL Server Architecture and Internals, published in 2003. Sure, the same information can be restated only so many ways, but there wasn't even an acknowledgement. You may not care. But I frequently rely on your excellent books and thought you should know.
Tyler Arnold
tarnold@claimiq.com - Anonymous
July 21, 2005
Thanks for pointing this out. I've contacted the site about it.