Using the NOWAIT option with the SQL Server RAISERROR statement

Hi,

Today we will discuss scenario where we execute sql statements or RAISERROR which include PRINT statements in between, but we really don't get to see the output of PRINT statement, it always come after sometime.

Use WITH NOWAIT clause of the RAISERROR statement. It may be surprising but using RAISERROR doesn’t require that there is an error condition. If the severity level given to RAISERROR is 0 through 10 SQL Server treats the RAISERROR as a plain message and not an error at all. Execution continues with the next statement, even if there is a TRY/CATCH block or if SET XACT_ABORT is ON. Use the combination of a RAISERROR severity of 0 to 10 and the WITH NOWAIT clause for a statement that sends output to the Messages windows immediately, like this one:

RAISERROR ('Not an error message!', 0, 1) WITH NOWAIT

When query results are being sent to a grid, these panes are shown as soon as command execution begins and the Messages window is hidden unless there are no results. There are two ways to address this. One option is to send the results to text using either the menu or CTRL+T. The other option is to allow results to go to the grid and click on the messages window or use the SSMS menu command Window/Next Pane, which by default is tied to the F6 key or Shift+F6 in Query Analyzer.

Once you've sent Results to Text with CTRL+T try this script:

DECLARE @time char(8)
PRINT '1- Before anything else ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2- RAISERROR before W/O NOWAIT %s', 0, 1, @time)
WAITFOR DELAY '00:00:05'
PRINT '3- After the first delay ' + convert (varchar(30), getdate(), 8)
SET @time = convert (varchar(30), getdate(), 8)
RAISERROR ('4- RAISERROR with NOWAIT %s', 0, 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:10'
PRINT '5- After the second delay ' + convert (varchar(30), getdate(), 8)

What you’ll see is something like this result:

1- Before anything else 15:17:58
2- RAISERROR before W/O NOWAIT 15:17:58
3- After the first delay 15:18:03
4- RAISERROR with NOWAIT 15:18:03
These results show up in 5 seconds. Neither the PRINT statements on lines 1 and 3 nor the RAISERROR on line 2 show up before the RAISERROR WITH NOWAIT on line 4 is executed. Then after another 10 seconds you’ll see:

5- After the second delay 15:18:13

As you can see from the time stamps, lines 1 and 2 were executed at 15:17:58 then there was a 5 second delay until 15:18:03 when lines 3 and 4 are executed and appear. It's the NOWAIT clause on the RAISERROR that produces line 4 that forces lines 1 through 4 to the message window.

One way to make the NOWAIT clause convenient is to write it into a simple stored procedure like this.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[ns_log_nowait]
@Msg nvarchar(2047)
/* Send a message to the caller so that it's available  immediately.
exec dbo.ns_log_nowait 'This is the immediate message'
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
compare this to print 'before the call'
raiserror ('this is the msg', 0, 1)
waitfor delay '00:00:10'
print 'This is after a 10 second delay'
***********************************************************/
AS
RAISERROR (@Msg, 0, 1) WITH NOWAIT
go
GRANT EXECUTE on [dbo].[ns_log_nowait] to PUBLIC
go

RAISERROR with severity under 11 isn’t treated as an error in a TRY/CATCH construct and NOWAIT has no effect on this behavior. To see that RAISERROR with severity 0 is treated like a print statement try this script in SSMS:

DECLARE @time char(8)

BEGIN TRY
PRINT '1 In the TRY block ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2 RAISERROR with NOWAIT %s', 0 , 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:05'
END TRY
BEGIN CATCH
PRINT '3 In the CATCH block ' + convert (varchar(30), getdate(), 8)
PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER()) + ' Severity = ' + convert (varchar, ERROR_SEVERITY())+ ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH
PRINT '4 After the CATCH block ' + convert (varchar(30), getdate(), 8)

The output from this script is shown here:

1 In the TRY block 15:22:49

2 RAISERROR with NOWAIT 15:22:49
4 After the CATCH block 15:22:54

Notice that lines 3 and 3A, in the CATCH block aren't reached even though a RAISERROR was executed. However, if you change the severity on the RAISERROR to 11, the statement is treated as an error. Here’s the script again with only the severity of the RAISERROR changed:

DECLARE @time char(8)

BEGIN TRY
PRINT '1 In the TRY block ' + convert (varchar(30), getdate(), 8)
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('2 RAISERROR with NOWAIT %s', 11, 1, @time) WITH NOWAIT
WAITFOR DELAY '00:00:05'
END TRY
BEGIN CATCH
PRINT '3 In the CATCH block ' + convert (varchar(30), getdate(), 8)
PRINT '3A Error Number = ' +convert (VARCHAR, ERROR_NUMBER())+ ' Severity = ' + convert (varchar, ERROR_SEVERITY())+ ' Msg = ''' + ERROR_MESSAGE() + ''''
END CATCH
PRINT '4 PRINT after the CATCH block ' + convert (varchar(30), getdate(), 8)
 
Which produces this output:

1 PRINT in the TRY block 15:24:33

3 PRINT in the CATCH block 15:24:33
3A Error Number = 50000 Severity = 11 Msg = '2 RAISERROR with NOWAIT 15:24:33'
4 PRINT after the CATCH block 15:24:33

Note that the RAISERROR statement is handled by the TRY/CATCH and doesn't produce message output on its own. Instead the error is handled by the CATCH block, which has code that prints the message on line 3A along with the severity.

Happy SQL Coding.......

Do let me know if you have any comments/feedback.......