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:
Yes! this is good
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!