SQL Server – Catch the Errors Details of Stored Procedure

Catch the Errors Details of Stored Procedure

When we write a User Defined Stored Procedure, it is quite natural that it will throw errors when we are testing it initially. Generally we can see the error generated and solve it easily. But many times the error generated are cryptic and very difficult to interpret. Debugging becomes a big headache.

The errors generated are even displayed as numbers. Now how a DBA is suppose to understand the errors by just looking at the error numbers?

What we can do is use TRY CATCH blocks in the User Defined Stored Procedure. Write the actual procedure code in TRY block and use CATCH block to deal with the error thrown by code in TRY block. Then we need to render the error and display it in more readable form.

Check out the CATCH block in USP_myProcedure below:

— The Procedure with CATCH block code.

ALTER PROCEDURE [dbo].[USP_myProcedure]

AS

BEGIN

BEGIN TRY

— Procedure actual code goes here

END TRY

BEGIN CATCH

— It will catch the error and diplay it in more readable form

DECLARE @ErrorSeverity INT,

@ErrorNumber INT,

@ErrorMessage NVARCHAR(4000),

@ErrorState INT

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

IF @ErrorState = 0

SET @ErrorState = 1

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)

END CATCH

END

It is good practice to including the above CATCH code in all the User Defined Stored Procedure.

Good Luck!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: