Category: SQL

SQL Error Handling

During the pre-SQL Server 2005 days, we had to handle errors via transactions. At times, this could prove challenging when dealing with specific errors. Some might fall through making debugging difficult.

Fortunately, the team at Microsoft copied the try/catch approach from Visual Studio to SQL Server.

Here is the deprecated approach:

BEGIN TRANSACTION

DELETE FROM tblBlah
WHERE DATESENT <= @DATE IF @@ERROR = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

Here is the current best practice:

BEGIN TRY
BEGIN TRANSACTION

DELETE FROM dbo.tblBlah
WHERE DATESENT <= @DATE COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

END CATCH

Shhh! Secret Procedures!

I don’t have to tell you that the folks at Microsoft can be quite sneaky so, in case you don’t already know, there are a number of unofficially documented SQL stored procedures that can make your life not only better, but often cursor-free.

The one that is most helpful to me is entitled “sp_msforeachtable.” Using this stored procedure you can accomplish the same tasks on each table.

You can Google it for more information but a short and simple article can be found at DatabaseJournal.com:

“Beginning with version 6.5 of SQL Server, Microsoft provides a stored procedure called sp_MSreachfortable. Using the question mark as a place holder for all table names, the procedure will do the same as the above query in a single line. You can replace the above cursor with this :

sp_MSforeachtable @command1=”print ‘?’ dbcc checktable (‘?’)”

For more handy secrets, do a search on “undocument sql server stored procedures” or http://lmgtfy.com/?q=undocumented+sql+server+stored+procedures .

Disabling and Enabling Table Constraints

Back in the ages of pre-Sql Server 2005, we had to drop and re-create constraints to modify certain data. I guess it was habit when I found myself beginning to do this today. Fortunately, I remembered the newer-improved way (of course, I DID have to look it up). Instead of dropping and re-creating constraint,s they can simply be disabled and enabled:

ALTER TABLE testTable NOCHECK CONSTRAINT FK_testTable_Task

–do your data deletes, etc here

ALTER TABLE testTable CHECK CONSTRAINT FK_testTable_Task

Voila, no risky dropping involved. Of course, be careful when manipulating your data in such a manner. This should not be normal practice.