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.
