Thursday, February 7, 2008

Today's Puzzle: Variable '@daffy' not found

Why does the following SQL give the error message "Variable '@daffy' not found"?

CREATE PROCEDURE mickey ( IN  @minnie INTEGER )
BEGIN
DECLARE @minnie INTEGER;
DECLARE @daffy INTEGER;
EXCEPTION
WHEN OTHERS THEN
SET @daffy = 1;
END;

CALL mickey ( 1 );

Add to Technorati Favorites

2 comments:

stuffittrans said...

The following line:

DECLARE @minnie INTEGER;

causes an exception (you cannot have two @minnie variables), so the next line

DECLARE @daffy INTEGER;

never gets executed.

Breck Carter said...

stuffittrans, you are correct, with the additional explanation that since @daffy doesn't exist when the EXCEPTION is raised, the reference to @daffy inside the exception handler raises a second exception and that's the message which is displayed. In a big procedure with several hundred lines, that can be truly confusing, at least to me. If you're going to Techwave, hit me up for a drink, I'll be the attendee riding a motorized scooter 'round the Mandalay :)