THROW in SQL Server

THROW is a modern error-handling statement introduced in SQL Server 2012. It is used to raise custom errors and re-throw original errors inside TRY…CATCH. It also preserves error number, message, severity, and line number. THROW is a modern SQL Server error-handling statement that preserves original error details, is ideal for TRY–CATCH blocks, and should replace RAISERROR in production systems.

Syntex

THROW error_number, message, state;

Here

Ø  error_number must be ≥ 50000

Ø  message → NVARCHAR(2048)

Ø  state → 1 to 255

Ø  Severity is always 16

See the example

DECLARE @Salary INT = -5000;  IF @Salary < 0     THROW 50002, 'Salary cannot be negative', 1;

See the demo here we Capture full error details in a log table and Re-throw the original error so application gets correct error and monitoring tools work to debugging is accurate.

For the demo we are creating a Log table and also creating a SP where we log the error during the SP execution failed.

CREATE TABLE dbo.errorlog   (      errorlogid     INT IDENTITY(1, 1) PRIMARY KEY,      errornumber    INT,      errorseverity  INT,      errorstate     INT,      errorline      INT,      errorprocedure SYSNAME,      errormessage   NVARCHAR(4000),      loginname      SYSNAME,      hostname       SYSNAME,      appname        SYSNAME,      errortime      DATETIME2 DEFAULT Sysdatetime()   );

Now creating SP

CREATE PROC dbo.transferamount  (   @FromAccount INT,   @ToAccount   INT,   @Amount      MONEY ) AS   BEGIN BEGIN try       BEGIN TRAN;       -- Business validation       IF @Amount <= 0 THROW 50001, 'Transfer amount must be positive', 1;       -- Debit       UPDATE accounts       SET    balance = balance - @Amount       WHERE  accountid = @FromAccount;              -- Credit       UPDATE accounts       SET    balance = balance + @Amount       WHERE  accountid = @ToAccount;              COMMIT;     END try BEGIN  catch       -- Safety rollback       IF @@TRANCOUNT > 0 ROLLBACK;       -- Log full error context       INSERT INTO dbo.errorlog                   (                               errornumber,                               errorseverity,                               errorstate,                               errorline,                               errorprocedure,                               errormessage,                               loginname,                               hostname,                               appname                   )                   VALUES                   (                               Error_number(),                               Error_severity(),                               Error_state(),                               Error_line(),                               Error_procedure(),                               Error_message(),                               Original_login(),                               Host_name(),                               App_name()                   );              --Re-throw original error (MOST IMPORTANT)       THROW;     END     catch END;

 Running this SP

EXEC dbo.TransferAmount      @FromAccount = 1,      @ToAccount   = 2,      @Amount      = -500;

Now we are seeing the log table.