How do you use the THROW statement to raise an error and terminate a batch?
Posted by QuinnLw
Last Updated: June 07, 2024
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.
Related Content
Employee Raise Program in C++
Employee Raise Program in C++
Samath | Nov 03, 2014