Pages

Feb 6, 2012

MS-SQL stored procedure Error handling in n-tier applications

Stored procedure to handle and re-throw ms-sql errors when executing SPs:
/************************************************************
 * AUTHOR: Nitin Sawant     
 * DATE: Feb 03, 2012     
 * DESCRIPTION: re-raise the error message   
 * REF: http://www.sommarskog.se/error_handling_2005.html 
 ************************************************************/
CREATE PROCEDURE SP_Handle_Error
AS
BEGIN 
 DECLARE @errmsg   nvarchar(2048),
   @severity tinyint,
   @state    tinyint,
   @errno    int,
   @proc     sysname,
   @lineno   int
            
 SELECT @errmsg = error_message(), @severity = error_severity(),   -- 10
     @state  = error_state(), @errno = error_number(),
     @proc   = error_procedure(), @lineno = error_line()
        
 IF @errmsg NOT LIKE '***%'                                        -- 11  
 BEGIN 
    SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '') + 
      ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
      ltrim(str(@errno)) + ': ' + @errmsg
    RAISERROR(@errmsg, @severity, @state)
 END
 ELSE
    RAISERROR(@errmsg, @severity, @state)
END
Usage:
/****************************************************
 * AUTHOR: Nitin Sawant   
 * DATE: Feb 03, 2012   
 * DESCRIPTION: Divide by zero exception
 * URL: http://www.nitinsawant.com  
 ****************************************************/
CREATE PROCEDURE SP_Test
AS
BEGIN
 BEGIN TRANSACTION;

 BEGIN TRY
  SET NOCOUNT ON --prevents SQL Server from sending row counts from INSERT, UPDATE, DELETE and MERGE statements to the client
  SET XACT_ABORT ON --make sure that any user-defined transaction is rolled back

  --Do your custum stuff
  SELECT 10/0 --divide by zero exception
 END TRY
 BEGIN CATCH
  --PRINT @@TRANCOUNT
  IF @@TRANCOUNT > 0 --check for open  transactions and roll them back
   ROLLBACK TRANSACTION;

  EXEC SP_Handle_Error --re-raise the error message
  RETURN 55555 --safety precaution to make sure that your procedure returns a non-zero value if no called caught the error raised in SP_Handle_Error
 END CATCH;

 IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
END
GO
EXEC SP_Test
Output:
Msg 50000, Level 16, State 1, Procedure SP_Handle_Error, Line 20
*** [SP_Test], Line 10. Errno 8134: Divide by zero error encountered.