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.
My thanks to Lam for catching this mistake and letting me know about it.