Doug Seven's Blog

Tech-Ed & My Dress Rehearsals

Its about a month and a half before Tech-Ed 2004 and I have now practiced my “SOL Server Data Access Developer Don'ts (10 Things You Currently Do That You Shouldn't)” talk that I will be doing in Tech-Ed 2004. My live practice sessions were in Tampa, FL, San Diego, CA and Olympia, WA. The attendance for each session varied, but it was between 35-75.

I got some really great feedback and tweaked the session a bit each time. Last night in Olympia, one of the attendees pointed out that one of my slides showing a stored procedure would actually not work as I was explaining it. The slide showed how to implement error handling in a stored procedure.

Here is the SPROC on the slide:

CREATE PROCEDURE dbo.Users_Insert
@Username VarChar (20)
AS
SET NOCOUNT ON
DECLARE @Error Int
SET @Error = 0 -- Success

INSERT Users (Username) VALUES (@Username)

IF (@@ERROR <> 0)
  BEGIN
  IF (@@ERROR == 547)
    BEGIN
      SET @Error = 32 -- Failed. Username already in use
      GOTO abort
    END 
   ELSE
    BEGIN
      SET @Error = 1 –- Unspecified error
    END
  END
abort:
SET NOCOUNT OFF
RETURN @Error
GO

The problem is that when I reference @@ERROR in the IF evaluation, it resets @@ERROR, so the rest of the error handling will fail.

Here is the corrected version of the slide:

CREATE PROCEDURE dbo.Users_Insert
@Username VarChar (20)
AS
SET NOCOUNT ON
DECLARE @Err Int
DECLARE @ReturnCode Int
SET @ReturnCode = 0 -- Success

INSERT Users (Username) VALUES (@Username)

SET @Err = @@ERROR
IF (@Err <> 0)
  BEGIN
    IF (@Err == 547)
    BEGIN
      SET @ReturnCode = 32 -- Failed. Username already in use
      GOTO abort
    END
  ELSE
    BEGIN
      SET @ReturnCode = 1 –- Unspecified error
    END
  END
abort:
SET NOCOUNT OFF
RETURN @ReturnCode
GO

In the corrected procedure I set the @@ERROR value to my own variable, @Err. This enables multiple references to the error value without inadvertently resetting @@ERROR, or getting a new @@ERROR value where there are multiple statements.