How To Return Error Message In Stored Procedure

Capturing the exception occurred in a stored procedure and returning the error message to the calling application or procedure is an important part in SQL Server programming. Just like other programming languages, SQL Server also has TRY – CATCH option for error handling and to return error message. Here is the syntax and an example of using TRY – CATCH blocks to capture the un-handled exception in a stored procedure and return the error details.

Syntax

/** Create Stored Procedure **/

DECLARE @Error nvarchar(MAX);

/** More Variable Declarations & Code **/

BEGIN TRY

	/** Code which needs to handled for exception. **/

END TRY
BEGIN CATCH
	SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' +
		'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' +
		'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' +
		'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' +
		'Error Message: ' + ERROR_MESSAGE()
	
	BREAK
END CATCH

/** Any other code **/

Example

In this example, we will create a stored procedure with TRY and CATCH blocks. In the TRY block, we will have a SQL statement which will raise an exception. The CATCH block will catch the exception’s error number, severity, state, line of error and the message and store them in an out parameter variable. Using the out parameter variable, we can get the error details outside the stored procedure.

/** Create Stored procedure **/
CREATE PROCEDURE mtb_SampleExceptionHndling
	@Error NVARCHAR(MAX) OUTPUT
AS
BEGIN
	BEGIN TRY
		SELECT 5/0;
	END TRY
	BEGIN CATCH
		SET @Error = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '; ' + Char(10) +
		'Error Message: ' + ERROR_MESSAGE()
	END CATCH
END
GO

/** Execute Stored Procedure **/
DECLARE @ErrorMsg NVARCHAR(MAX)
EXEC mtb_SampleExceptionHndling @Error = @ErrorMsg output
Select @ErrorMsg
GO

/** Result **/
-----------

(0 rows affected)

-----------

Error Number: 8134; 
Error Severity: 16; 
Error State: 1; 
Error Line: 7; 
Error Message: Divide by zero error encountered.

(1 row affected)

Note

1. TRY – CATCH will not capture any exception or warnings which are having the error severity of less than 10.
2. They wont capture syntax errors or errors generated during statement level recompilation.

Read more about implementing TRANSACTION along with exception handling using TRY – CATCH blocks.

Reference

• About TRY-CATCH blocks in T-SQL at Microsoft Docs.