For the SQL developer who isn't also a programmer (are there any of you left out there?), the try catch block is a very useful concept from programming which can serve you very well in your sql scripts.
Take the following scenario
begin a transaction
do something and check if it went down okay
do something else and check if it went down okay
do something else and check if it went down okay
the last one didn't?! Something went wrong
Rollback!
Commit
This works, but it lacks:
1. Ease of reading. If I'm a sql developer I can make sense of this, but it isn't necessarily clear to anyone else. Why is that commit hanging around at the end?
2. A mechanism for grouping all your steps together when things go wrong. What if you want to do something else (aka log the attempt) after you rollback? Yes, you can use an IF statement, but again, your code becomes more unreadable.
Enter the Try Catch. Let's take the same statement
begin a transaction
begin a try
do something
do something
do something
Commit
end try
begin catch
Rollback
end catch
Much easier to read. Much cleaner to use.
Let's look at a real world example
begin transaction
begin try
delete from SomeTable where Id = 24
delete from SomeOtherTable where Id = 22
--this will fail as the table above has a foreign key constraint on this table's records
--and thus must be deleted first
delete from FinalTable where Id=26
commit transaction
end try
begin catch
rollback transaction
end catch
This was good, but we can do better. My queries are protected, but I'm still no wiser as to what the problem was in the first place. Nothing could be simpler. Inside the catch, we have access to the error which threw the exception in the first place. You can do what you like with it, even re-throw it. TransactSQL provides 3 functions to get at our error message and details:
ERROR_MESSAGE() - This returns the error message
ERROR_SEVERITY() - This returns the severity, if you want it
ERROR_STATE() - This returns the error state, though I've never needed it to date.
declare
@ErrorMessage NVARCHAR(4000)
,@ErrorSeverity INT
,@ErrorState INT
begin transaction
begin try
delete from SomeTable where Id = 24
delete from SomeOtherTable where Id = 22
--this will fail as the table above has a foreign key constraint on this table's records
--and thus must be deleted first
delete from FinalTable where Id=26
commit transaction
end try
begin catch
rollback transaction
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
--At this point you can do what you like with the error. I'm just rethrowing it after my rollback
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
end catch
So what I now get back is the following:
(1 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 16, State 0, Line 28
The DELETE statement conflicted with the REFERENCE constraint "FK_SomeOtherTable_FinalTable". The conflict occurred in database "ChrisDB", table "dbo.SomeOtherTable", column 'FinalTableId'.
And a query of my tables shows no rows were deleted due to the rollback.
Hope this helps someone out there.
No comments:
Post a Comment