Stored Procedure Error Handling Pattern

Thanks to SQL Server MVP Erland Sommarskog, we got a unified and reliable error and transaction handling in stored procedures. Here I briefly demonstrate how to do.

1. Create the error handler sp:
-- =============================================
-- Author: Erland Sommarskog
-- Description: Error Handler SP
-- Usage: To be called inside CATCH block of a stored proc to reraise error. Error Line Number can be precisely checked by using [sp_helptext] system stored proc., e.g. sp_helptext '[dbo].[the_sp_name]'
-- Reference: General Pattern for Error Handling (http://www.sommarskog.se/error_handling/Part1.html)
-- =============================================
CREATE PROCEDURE error_handler_sp AS
BEGIN
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
         
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
     
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' +
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)
END
GO

2. Employ the error handling pattern in your stored procs:
/* TESTING TABLE */
CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))
GO

/* ERROR HANDLING PATTERN */
CREATE PROCEDURE insert_data @a int, @b int AS
BEGIN
   SET XACT_ABORT, NOCOUNT ON
   /*
    * The first line in the procedure turns on XACT_ABORT and NOCOUNT in single statement. This line is the only line to come before BEGIN TRY.
* Everything else in the procedure should come after BEGIN TRY: variable declarations, creation of temp tables, table variables, everything.
* Even if you have other SET commands in the procedure (there is rarely a reason for this, though), they should come after BEGIN TRY.
*/
   BEGIN TRY
     /* Non-transactional statements, e.g. variable declarations */
      BEGIN TRANSACTION /* If your procedure does not perform any updates or only has a single INSERT/UPDATE/DELETE/MERGE statement, you typically don't have an explicit transaction at all. */
 /* Transactional statements */
      INSERT sometable(a, b) VALUES (@a, @b) -- sample
      INSERT sometable(a, b) VALUES (@b, @a) -- sample
      COMMIT TRANSACTION
 /* Non-transactional statements, e.g. final SELECT to return data or assign values to output parameters */
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION -- Rolls back any open transaction
      EXEC error_handler_sp -- Reraises the error
      RETURN 55555 -- any error return code you defined on your application (Non-zero, zero is usually understood as success)
   END CATCH /* NEVER have any code after END CATCH for the outermost TRY-CATCH */
END
GO

/* TEST CASES */
-- Outer SP
CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      EXEC error_handler_sp
      RETURN 55555
   END CATCH
GO
EXEC insert_data 9, NULL
EXEC insert_data 8, 8
EXEC outer_sp 8, 8
EXEC outer_sp null, null
EXEC sp_helptext '[insert_data]' -- check error line number of the error throwing stored proc.