Create Immediate Messages
In SQL, when you don’t want to change the result set, but you want to provide some messages, for example, when debugging a query in SSMS, it can be really helpful to use the PRINT statement. What you’ll start to notice though is that the print statement doesn’t always print straight away. Usually this is ok, but if you have a long running query, or a maintenance task, you might want some feedback sooner. So how do you get an immediate message?
The easiest (although not obvious) way is to use the SQL RAISEERROR command:
RAISEERROR requires 3 parameters – the error message, the severity and the state.
- Error Message: hopefully self explanatory, it’s the message you want the user to see.
- Severity: this needs to be 0 if you don’t want an actual error to be raised. 0 means that it’s just informational.
- State: this allows you to indicate where the code is up to when the error is thrown – not useful for what we’re doing here
- WITH NOWAIT: this is the other part of the magic. Without this, you’ll just get your messages at the end, but if you include it then it will display as soon as the command is executed