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

Question

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!!

Solution 1

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.

Solution 2

You could use THROW (available in SQL Server 2012+):

THROW 50000, 'Your custom error message', 1
THROW <error_number>, <message>, <state>

MSDN THROW (Transact-SQL)

Differences Between RAISERROR and THROW in Sql Server

Solution 3

SQL has an error raising mechanism

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

Just look up Raiserror in the Books Online. But.. you have to generate an error of the appropriate severity, an error at severity 0 thru 10 do not cause you to jump to the catch block.

Solution 4

You're looking for RAISERROR.

From MSDN:

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

CodeProject has a good article that also describes in-depth the details of how it works and how to use it.

Solution 5

THROW (Transact-SQL)

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

Please refer the below link

T-SQL Throw Exception

View more solutions

Comments

[MarceloBarbosa]

Can't use 'THROW' within a function. Sad.