In SQL Server, the THROW statement is used to raise an exception and transfer control to a CATCH block. It allows you to raise custom errors or propagate existing errors from within your code. Here’s how you can use the THROW statement to raise an exception:
Basic Syntax
To raise a custom error using THROW, you can use the following syntax:
THROW [error_number], [message], [state]
- error_number: An integer number that represents the error. It must be a valid error number in the range from 50000 to 2147483647 for user-defined errors.
- message: A string that describes the error. You can also use a message defined in sys.messages.
- state: An integer that represents the state of the error. This can be any value from 0 to 255.
Example: Raising a Custom Exception
Here's an example that demonstrates how to use THROW to raise a custom exception:
BEGIN TRY
-- Some code that might cause an error
DECLARE @Num INT = 0;
DECLARE @Result INT;
-- Attempting to divide by zero
SET @Result = 10 / @Num;
END TRY
BEGIN CATCH
-- Handle the error and raise a custom exception
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
-- Get the error details
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Raise a custom error
THROW 50001, 'A custom error occurred: ' + @ErrorMessage, 1;
END CATCH;
Explanation of the Example
1. BEGIN TRY: This block contains the code that might throw an error.
2. SET @Result = 10 / @Num;: This line intentionally causes a division by zero error.
3. BEGIN CATCH: This block will execute if an error occurs in the TRY block.
4. ERROR_MESSAGE(): Retrieves the message for the error that caused the CATCH block to run.
5. THROW 50001, 'A custom error occurred: ' + @ErrorMessage, 1;: Raises a custom error with a specific number and message.
Re-throwing Existing Errors
You can also use THROW without parameters to re-throw the caught error:
BEGIN CATCH
-- Re-throw the original error
THROW;
END CATCH;
This will preserve the original error number, message, and state, passing it to the higher level of the call stack.
Summary
- Use THROW to raise exceptions in SQL Server.
- Specify custom error numbers, messages, and states.
- THROW can also be used to re-throw caught errors without modifying their original properties.