Raise an error manually in T-SQL to jump to BEGIN CATCH block

exception, sql, sql-server, t-sql, try-catch

Solution

you can use `raiserror`. Read more details here

--from MSDN

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

EDIT If you are using SQL Server 2012+ you can use `throw` clause. Here are the details.

Problem

Is it possible to raise an error in a stored procedure manually to stop execution and jump to `BEGIN CATCH` block? Some analog of `throw new Exception()` in `C#`. Here is my stored procedure's body: ``` BEGIN TRY BEGIN TRAN -- do something IF @foobar IS NULL -- here i want to raise an error to rollback transaction -- do something next COMMIT TRAN END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRAN END CATCH ``` I know one way: `SELECT 1/0` But it's awful!!

Original source

Related problems