SQL Server 2005/2008's TRY/CATCH and constraint error handling
I was thrilled that T-SQL finally got the TRY/CATCH construct that many object-oriented languages have had for ages. I had been writing error handling code like this:
BEGIN TRANSACTION TransactionName
...
-- Core of the script - 2 lines of error handling for every line of DDL code
ALTER TABLE dbo.MyChildTable DROP CONSTRAINT FK_MyChildTable_MyParentTableID
IF (@@ERROR <> 0)
GOTO RollbackAndQuit
...
COMMIT TRANSACTION TransactionName
GOTO EndScript
-- Centralized error handling for the whole script
RollbackAndQuit:
ROLLBACK TRANSACTION TransactionName
RAISERROR('Error doing stuff on table MyChildTable.', 16, 1)
EndScript:
GO
...which gets pretty ugly when you have a script that does 5-10 or more such operations and it has to check for an error after every one. With TRY/CATCH, the above becomes:
BEGIN TRANSACTION TransactionName;
BEGIN TRY
...
-- Core of the script - no additional error handling code per line of DDL code
ALTER TABLE dbo.MyChildTable DROP CONSTRAINT FK_MyChildTable_MyParentTableID;
...
COMMIT TRANSACTION TransactionName;
END TRY
-- Centralized error handling for the whole script
BEGIN CATCH
ROLLBACK TRANSACTION TransactionName;
DECLARE @ErrorMessage NVARCHAR(4000) = 'Error creating table dbo.MyChildTable. Original error, line [' + CONVERT(VARCHAR(5), ERROR_LINE()) + ']: ' + ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = CASE ERROR_STATE() WHEN 0 THEN 1 ELSE ERROR_STATE() END;
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH;
GO
Much cleaner in the body of the script; we have to do more work in the CATCH (wouldn't it be nice if T-SQL had a THROW statement like C# to rethrow the exact same error that was caught?), but the core of the script that makes DDL changes is cleaner and more readable. The only serious downside I've found so far is when dropping a constraint, and you get a message like this without TRY/CATCH:
Msg 3728, Level 16, State 1, Line 1
'FK_MyChildTable_MyParentTableID' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
(In this case I'd check for the constraint before trying to drop it; this is just for illustration. One I've seen more often is trying to drop a primary key and recreate it on a parent table when there are foreign keys on child tables that reference the parent.)
TRY/CATCH shortens the above error to only "Could not drop constraint. See previous errors." with no previous errors shown. Now, some research will reveal why it couldn't drop the constraint, but TRY/CATCH is supposed to make error handling easier and more straightforward, not more obscure. The "See previous errors" line has always struck me as a lazy error message--I bet there's a story amongst seasoned SQL Server developers at Microsoft as to why this throws two error messages instead of one--so I imagine the real problem is in that dual error message more than the TRY/CATCH construct itself as it's implemented in T-SQL.
If anyone has a slick way to get that initial Msg 3728 part of the error, I'm all ears; I've seen several folks ask this question and not one answer yet.