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  gt  0         ROLLBACK TRAN     END CATCH   I know one way  SELECT 1 0 But it s awful

User · Answer

You could use THROW  available in SQL Server 2012     THROW 50000   Your custom error message   1 THROW  lt error number gt    lt message gt    lt state gt    MSDN THROW  Transact-SQL   Differences Between RAISERROR and THROW in Sql Server

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to exception-handling

Examples related to try-catch