Home Library Learn Downloads Support Community
Sign in | United States - English | |
3 out of 3 rated this helpful - Rate this topic

THROW (Transact-SQL)

Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2012.

Topic link icon Transact-SQL Syntax Conventions

Copy 

          

THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]
error_number

Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

message

Is an string or variable that describes the exception. message is nvarchar(2048).

state

Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be ended.

Differences Between RAISERROR and THROW

The following table lists differences between the RAISERROR and THROW statements.

RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.

A. Using THROW to raise an exception

The following example shows how to use the THROW statement to raise an exception.

Transact-SQL
Copy 
THROW 51000, 'The record does not exist.', 1;

B. Using THROW to raise an exception again

The following example shows how use the THROW statement to raise the last thrown exception again.

Transact-SQL
Copy 
USE tempdb;
GO
CREATE TABLE dbo.TestRethrow
(    ID INT PRIMARY KEY
);
BEGIN TRY
    INSERT dbo.TestRethrow(ID) VALUES(1);
--  Force error 2627, Violation of PRIMARY KEY constraint to be raised.
    INSERT dbo.TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH

    PRINT 'In catch block.';
    THROW;
END CATCH;

PRINT 'In catch block.';

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__TestReth__3214EC272E3BD7D3'. Cannot insert duplicate key in object 'dbo.TestRethrow'.

The statement has been terminated.

C. Using FORMATMESSAGE with THROW

The following example shows how to use the FORMATMESSAGE statement with THROW to throw a customized error message.

Transact-SQL
Copy 
DECLARE @Message NVARCHAR(2048);
SELECT @Message = FORMATMESSAGE(1127);
THROW 50001, @Message, 1;
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Transact-SQL Throw Examples
There are some more examples of using Throw at http://cm-bloggers.blogspot.com/2011/02/using-new-throw-keyword-in-sql-server.html
History