In long running scripts it's usefull to notify the client of the progress. Here's a simple solution.
Let's create a simple "long running" script:
-- this will return all 3 results to the client after 20 seconds (full execution time)
SELECT 1
WAITFOR DELAY '00:00:10'
SELECT 2
WAITFOR DELAY '00:00:10'
SELECT 3
But what if you want to notify the client when each select happened?
RAISERROR to the rescue:
SELECT 1
-- flush message to client
RAISERROR (N'After 1', -- Message text
10, -- Severity
1) -- State
WITH NOWAIT
WAITFOR DELAY '00:00:10'
SELECT 1.5
WAITFOR DELAY '00:00:10'
SELECT 2
-- flush waiting messages and resultset to client
-- so this will send 1.5 and 2 to the client
RAISERROR (N'After 2', -- Message text
10, -- Severity
1) -- State
WITH NOWAIT
WAITFOR DELAY '00:00:10'
SELECT 3
The thing to note here is the use of NOWAIT option.
This flushes all waiting resultsets and messages back to the client.
Also note the use of non fatal severity and state, since we don't want to cancel our script but only inform the user of the progress.
It comes in very handy when running script that can take a while indeed.
I've seen this mentioned before but i can never find it when i need it so if anyone knows the link post it in the comments.
