I'm doing some SQL stored procedure programming for the first time. The error handling is really, really primitive. T-SQL uses lots of global variables, which makes things really complex. For example, if you do an UPDATE, @@ROWCOUNT has the number of rows affected, and @@ERROR has the error code. However, if you look at @@ROWCOUNT, you'll reset @@ERROR. And, if you look at @@ERROR, you reset @@ROWCOUNT! What can you do? Well, you have to put both values into temporary variables in a single statement:

DECLARE @err int,

        @rowcount int

UPDATE ...

SELECT @err = @@ERROR, @rowcount = @@ROWCOUNT

Now, you can look at the local variables @err and @rowcount. Erland Sommarskog has a couple of great articles on SQL error handling here and here.