Pages

▼

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
at

2 comments:

  1. AnonymousFebruary 19, 2008 at 1:17 PM

    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.

    ReplyDelete
  2. Breck CarterFebruary 19, 2008 at 3:10 PM

    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 :)

    ReplyDelete