Using Try…Catch in SQL Server 2005

Try…Catch is one of the best approach for exception handling and SQL Server 2005 offers it with almost identical syntax as in C#, C++. Try Block contains instructions or SQL statements that may raise an exception whereas Catch Block handles this exception.

Syntax:


BEGIN TRY
{SQL Statements}
END TRY

BEGIN CATCH
{SQL Statements}
END CATCH


Error:

This following code snippet shows how to retrieve error details if any exception occurs.


SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;


Try…Catch Block with SQL Transaction:

Try…Catch Block can be effectively used to deal with COMMIT/ ROLLBACK SQL Transaction. The following Stored Procedure updates t_Project table for any specific valid ProjectID and inserts a row in to t_ProjectHistory table against this row modification in t_Project table. However, you can raise exception of your own for any invalid ProjectID as shown in the code snippet.


CREATE PROCEDURE usp_ModifyProjectComplete (@ProjectID int)
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @CountProjectID int

SELECT @CountProjectID = count(project_Id) FROM t_Project
WHERE project_Id = @ProjectID

IF @CountProjectID NOT EQUAL 1 RAISERROR (‘This is not valid Project No’,16,2,@ProjectID);

UPDATE t_Project
SET project_Status = ‘COMPLETE’
WHERE project_Id = @ProjectID;

INSERT INTO t_ProjectHistory (projectHstory_ProjectId, projectHistory_Date,projectHistory_Status)
VALUES (@ProjectID,GETDATE(),’COMPLETE’);

COMMIT
END TRY

BEGIN CATCH
IF @@TRANCOUNT NOT EQUAL 0 ROLLBACK

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH


Last but not the least, you have all the error information available in catch Block so that you can provide treatment or just forward the error to the next level.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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: