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

Answer 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.