Wednesday, October 22, 2008

Error Handling in SQL Server 2005 Stored Procedure using Try Catch Block

I am fascinated by the easy and safer way SQL Server 2005 provides for error handling. Traditionally we used @@ERROR for this purpose, along the BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION blocks. Below is one example.

Traditional Error Handling in SQL Server 2000/2005

ALTER PROCEDURE dbo.DeleteFromMyTable

AS

/* SET NOCOUNT ON */

BEGIN TRANSACTION

DELETE FROM myTable1 WHERE myColumn='myColumnValue'; --delete transaction1

IF @@ERROR>0 --some error occured

BEGIN

ROLLBACK TRANSACTION

RETURN

END

ELSE --no error has occured till now, move ahead (do some more transactions)

BEGIN

BEGIN TRANSACTION

DELETE FROM myTable2 WHERE myColumn='myColumnValue'; --delete transaction2

IF @@ERROR>0 --some error occured at this point

BEGIN

ROLLBACK TRANSACTION
--this rollbacks previous delete action also

RETURN

END

ELSE
--both the delete transactions successful !

BEGIN

--so commit the transactions

COMMIT TRANSACTION

RETURN

END

END

All these stuffs work (perfectly!). But oh! SQL Server 2005! There exists more easy and interesting way. If you are an asp.net programmer or the one who works with front end, there is exactly like what a developer faces with error handling. Guess what? You are right, it is Try..Catch block. Yes Try..Catch block has been introduced in SQL Server 2005, and it provides better way to performing transactions like above in SQL Server database.

Latest Error Handling in SQL Server 2005

ALTER PROCEDURE dbo.DeleteFromMyTable

AS

/* SET NOCOUNT ON */

BEGIN TRY

BEGIN TRANSACTION

DELETE FROM myTable1 WHERE myColumn='myColumnValue'; --delete transaction1

DELETE FROM myTable2 WHERE myColumn='myColumnValue'; --delete transaction2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 --some error has occurred

ROLLBACK TRAN --so rollback all transactions

--you raise error or fill output variable here

--for front end reference in your asp.net webpage

END CATCH

See how easy and better way? I found this Try..Catch has made sql programming like front end programming. Happy Programming!

1 comments:

Post a Comment

Hope you liked this post. You can leave your message or you can put your valuable suggestions on this post here. Thanks for the sharing and cooperation!

Popular Posts

Recent Articles