Programming

Categories

  • No categories

SEARCH

I’m Attending

CodeMash

Building SQL Server T-SQL with Error Handling

Posted in: Programming by Steve on December 1, 2010

Today, my manager asked me how I build my T-SQL scripts when I need to update a bunch of data in SQL Server.  I hadn’t really thought much about it, but this is what I normally use.

DECLARE @ErrorCode INT

BEGIN TRANSACTION

	--UPDATE A TABLE OR SOMETHING

	SELECT @ErrorCode = @@ERROR
	IF (@ErrorCode <> 0) GOTO PROBLEM
	
	--DO SOMETHING ELSE
	
	SELECT @ErrorCode = @@ERROR
	IF (@ErrorCode <> 0) GOTO PROBLEM	
	
COMMIT TRANSACTION
	
PROBLEM:
IF (@ErrorCode <> 0)
BEGIN
	PRINT 'Error Executing Script: Error Code: ' + @ErrorCode
	ROLLBACK TRANSACTION
END

Not sure how anyone else does it, but that’s how I roll.  Any suggestions on how this could be improved? 

Comments

2 Comments
  1. That is pretty close to my implementation of using errors. The only major piece that I would change is that most errors need more than to simply be printed. I use the sql RAISERROR in most cases. The main reason for this is that it not only passes good exceptions back to your application, but also it will make automated scripts in SQL show failure messages rather than success.

    Comment by Nathan Blevins on December 1, 2010 at 10:06 am

  2. @Nathan – I would agree if this had to be executed in the context of an application that needed feedback. If this was just executed in SSMS, this is sufficient for just a single execution of the script and not for reuse.

    Comment by Steve on December 1, 2010 at 10:12 am