SQL Server - Handling Errors in SQL Server 2012

Handling Errors in SQL Server 2012

SQL Server 2012, you now have a replacement for RAISERROR, the THROWstatement, which makes it easier than ever to capture the error-related data.

Working with the THROW Statement

ALTER PROCEDURE UpdateSales

  @SalesPersonID INT,

  @SalesAmt MONEY = 0

AS

BEGIN

  BEGIN TRY

    BEGIN TRANSACTION;

      UPDATE LastYearSales

      SET SalesLastYear = SalesLastYear + @SalesAmt

      WHERE SalesPersonID = @SalesPersonID;

    COMMIT TRANSACTION;

  END TRY

  BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

 

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

 

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));

    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

 

    THROW;

  END CATCH

END;

GO

~

USE AdventureWorks2012; GO   IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL DROP PROCEDURE UpdateSales; GO   CREATE PROCEDURE UpdateSales   @SalesPersonID INT,   @SalesAmt MONEY = 0 AS BEGIN   BEGIN TRY     BEGIN TRANSACTION;       UPDATE LastYearSales       SET SalesLastYear = SalesLastYear + @SalesAmt       WHERE SalesPersonID = @SalesPersonID;     COMMIT TRANSACTION;   END TRY   BEGIN CATCH     IF @@TRANCOUNT > 0     ROLLBACK TRANSACTION;       DECLARE @ErrorNumber INT = ERROR_NUMBER();     DECLARE @ErrorLine INT = ERROR_LINE();     DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();     DECLARE @ErrorSeverity INT = ERROR_SEVERITY();     DECLARE @ErrorState INT = ERROR_STATE();       PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));     PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));       RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);   END CATCH END; GO ~ If the UPDATE statement runs successfully, the SalesLastYear value is updated and the operation is completed, in which case, the code in the CATCH block is never executed.  the @@TRANCOUNT function to determine whether any transactions are still open. @@TRANCOUNT is a built-in SQL Server function that returns the number of running transactions in the current session. In this case, there should be only one (if an error occurs), so I roll back that transaction.