If not Exists logic

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 corre开发者_如何学Cct 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