In SQL Server, the THROW statement is used to raise an error and terminate a batch. You can use it to signal an error condition in your code and return a specific error message, along with an optional error number and state. The THROW statement can be very useful in stored procedures, triggers, and batches to gracefully handle errors.
Here’s how you can use THROW to raise an error:
1. Basic Usage: You can use THROW without any arguments to re-throw the original error if you are within a CATCH block.
2. Raising a New Error: To raise a new error, you can specify an error number, message, and state.
Syntax
THROW [ { error_number | @local_variable } , 'message_string' , state ]
Example Usage
1. Basic Example of Raising an Error:
BEGIN TRY
-- Some code that may error
SELECT 1 / 0; -- This will cause a divide by zero error
END TRY
BEGIN CATCH
-- Handle the error
THROW; -- Re-throws the original error
END CATCH;
2. Raising a Custom Error:
BEGIN TRY
-- Some logic that checks for a condition
IF NOT EXISTS (SELECT * FROM Users WHERE UserID = @UserID)
BEGIN
-- Raise a custom error if the UserID does not exist
THROW 50001, 'UserID not found.', 1;
END
END TRY
BEGIN CATCH
-- Handle the error
SELECT ERROR_MESSAGE() AS ErrorMessage, ERROR_NUMBER() AS ErrorNumber;
END CATCH;
Explanation:
- The above snippet raises a custom error with:
- 50001 as the error number (this can be any number greater than 50000).
- 'UserID not found.' as the error message.
- 1 as the state.
When you execute the code, if the UserID does not exist, the custom error will be raised, and it will be caught in the CATCH block, where you can take further action like logging the error or returning a message.
Notes:
- The THROW statement always terminates the execution of the batch.
- You can raise errors with your own error numbers (greater than or equal to 50000) for user-defined errors.
- You can use THROW in any context where SQL Server allows expressions, such as procedures, functions, and triggers.