Using RAISERROR with NOWAIT
Published: Feb 05, 2016
Now we’ve used RAISERROR (as per my previous post) we need to address a specific issue which RAISERROR has and which we need to overcome in order to make this more useful. That would be that issue that SQL Server doesn’t show messages until the very end of execution. This will fix that issue.

Basically the NO WAIT extension will cause SQL Server to output the message to the window immediately and not wait until the end of execution.

This is quite hard to demonstrate in a blog post and therefore you’ll have to run these demos yourself in order to see the results… but they should quickly show you what I want you to see.

Let’s take a case scenario… we have a piece of custom code and we want to raise an alert or warning to the Messages window in SSMS if we’re looking at old data. Simple enough:

declare @date date = '2010-01-01'

waitfor delay '00:00:05'

if @date <= '2013-01-01'
begin
   raiserror
('Warning! Old Data', 1, 0)
end

waitfor
delay '00:00:05'

print 'Complete'


So what’s the problem?

Well if you run the code above you’ll notice that nothing appears in the Messages window until the entire code has executed… ie. After 10 seconds.

But what if you want to know about the warning straight away? Well you can use NOWAIT to achieve this:

declare @date date = '2010-01-01'

waitfor delay '00:00:05'

if @date <= '2013-01-01'
begin
   raiserror
('Warning! Old Data', 1, 0) with nowait
end

waitfor
delay '00:00:05'

print 'Complete'


And if you run that (and look in the Messages tab whilst executing it) you’ll see that the warning is raised at the correct time, therefore allowing you to get good progress updates or notifications about your code as you go rather than waiting until the end of execution to see what happened.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron