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.',
DECLARE @Stop int
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)
WAITFOR DELAY @PollingInterval
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.