I am doing something like this:
exec up_sp1 -- executing this stored procedure which populates the table sqlcmds ---Check the count of the table sqlcmds, ---if the count is zero then execute up_sp2, ----otherwise wait till the count becomes zero,then exec up_sp2 IF NOT EXISTS ( SELECT 1 FROM [YesMailReplication].[dbo].[SQLCmds]) BEGIN exec up_sp2 END What would the correct t-sql look like?
T-SQL has no WAITFOR semantics except for Service Broker queues. So all you can do, short of using Service Broker, is to poll periodically and see if the table was populated. For small scale this works fine, but for high scale it breaks as the right balance between wait time and poll frequency is difficult to achieve, and is even harder to make it adapt to spikes and lows.
But if you are willing to use Service Broker, then you can do much more elegant and scalable solution by leveraging Activation: up_sp1 drops a message into a queue and this message activates the queue procedure that starts and launches up_sp2 in turn, after the up_sp1 has committed. This is a reliable mechanism that handles server restarts, mirroring and clustering failover and even rebuilding of the server from backups. See Asynchronous procedure execution for a an example of achieving something very similar.
The Service Broker solution is surely the best - but there is a WAITFOR solution as well:
exec up_sp1; while exists (select * from [YesMailReplication].[dbo].[SQLCmds]) begin waitfor delay ('00:00:10'); -- wait for 10 seconds end; exec up_sp2; Try this:
DECLARE @Count int SELECT @Count = COUNT(*) FROM [YesMailReplication].[dbo].[SQLCmds]) IF @Count > 0 BEGIN exec up_sp2 END Why not keep it simple and self-documenting?
DECLARE @Count int; SELECT @Count = Count(*) FROM [YesMailReplication].[dbo].[SQLCmds] If @Count = 0 exec up_sp2 