Raising Errors in Transact-Sql

There seems to be quite a bit of confusion about the way errors should be raised in newer versions of Transact-Sql. The documentation for RAISERROR says: “New applications should use THROW instead.” It doesn’t say that RAISERROR is deprecated; when you look at the list of deprecated features what you find is that invoking RAISERROR without an error number is now deprecated, but it’s unclear whether the command itself will go.

RAISERROR is useful because it allows the level of an error levels to be set at run time. The code sample shows how a level 10 information error can be raised when a stored procedure succeeds. If it fails a level 16 error is raised, which in conjunction with transactions and a BEGIN..TRY..CATCH block can make sure that the partial changes are rolled back and the database stays consistent.

use Test
go
--set up test--
CREATE PROCEDURE usp_TestReturnZero
AS
BEGIN
RETURN 0
END
GO

CREATE PROCEDURE usp_TestReturnOne
AS
BEGIN
RETURN 1
END
GO

--now run "successful" stored procedure (returns 0)--
DECLARE @RC int
EXEC @RC = usp_TestReturnZero
--return code determines error level--
DECLARE @ErrLevel int = CASE WHEN @RC = 0 THEN 10 ELSE 16 END

IF @RC = 0
BEGIN
RAISERROR ('The stored procedure executed .', @ErrLevel, 1, 1)
END
ELSE
RAISERROR ('The stored procedure ERRORED.', @ErrLevel, 1, 1)

--run stored procedure UNSUCCESSFULLY (returns 1)--
DECLARE @RC int
EXEC @RC = usp_TestReturnOne
--return code determines error level--
DECLARE @ErrLevel int = CASE WHEN @RC = 0 THEN 10 ELSE 16 END

IF @RC = 0
BEGIN
RAISERROR ('The stored procedure executed.', @ErrLevel, 1, 1)
END
ELSE
RAISERROR ('The stored procedure ERRORED.', @ErrLevel, 1, 1)

--BACKOUT THE ABOVE CHANGES--
/*
USE Test
GO
DROP PROCEDURE usp_TestReturnZero
DROP PROCEDURE usp_TestReturnOne
*/

It’s not as straightforward to use THROW in the same way since you can’t set the error level. An alternative could be to use a PRINT statement in the first clause of the "[email protected] = 0" statement. This is fine unless you want the information errors logged in the PROD environment. RAISERROR allows you to specify WITH LOG if you have the right privileges, but a PRINT statement in PROD will be discarded. If you decide to use RAISERROR it would be a good idea to check further on whether the statement will be deprecated in future.

If you’ve decided to use THROW from now on, and you’re not familiar with it, there’s a tip which I think will be helpful. Always code it with a leading semi-colon. This is because a semi-colon termination is needed for the line above, but the error message when it’s missing is rather cryptic. See below:

DECLARE @Msg nvarchar(1000) = 'Message'
THROW 50000, @Msg, 1
/*
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near 'THROW'.
*/

DECLARE @Msg nvarchar(1000) = 'Message'
;THROW 50000, @Msg, 1
/*
works fine
*/

To save going back to the documentation over and over again wondering how you could mess up such a simple command, just prefix “THROW” with the semi-colon and you’ll be fine. Some people also prefix the “WITH” in a CTE, but that looks a bit “tricksy” and the error message identifies the problem correctly so I don’t think it’s as important.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s