Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.