• ronmoses (4/30/2013)


    So here's a question. If variables are initialized at parsing time - I take this to mean that the DECLARE is interpreted prior to the INSERT - why can't you declare a variable later in the code than it's used? You can't swap out the IF and ELSE branches in this example. Not that you'd want to do it, it's just an academic question.

    ron

    Oh, variables are set to null, not initialised, at parse time; initialisation to a real value (as opposed to leaving the variable marked as not having a real value at all - ie null) can only happen at execution time, even when that initialisation is syntactically part of the statement which declares the variable. Declarations happen when the smallest statement (so for example not an if...else statement, but one of the statements which are components of it) which includes the declaration is parsed, and parsing goes strictly to lines from top to bottom and within a line to characters from left to right, so the scope begins at the declaration and ends at the end of the batch. There is yet another (see my post a few posts above for the first one) error in BOL that claims that the scope is the batch in which the variable is declared; that is wrong, the scope begins at the declaration, not at the beginning of the batch - we know this is true because the parser will object to any use of the variable anywhere earlier in the batch than the declaration.

    To see that initialisation is at execution time not at parse try the following code:-

    declare @k int = 1;

    if @k = 0 begin declare @j-2 int = 2 end else if @j-2 = 2 begin declare @i int = 3 end

    select @i, @j-2, @k

    The values selected are NULL, NULL, 1. So the declarations of @j-2 and @i occured while the if...else statement was being parsed (since execution reaches neither of the declarations) but their initialisations - even though they were written as parts of the statements that contained the declarations - were not executed because execution didn't reach the declaration statements.

    edit: I think this is the first QotD that I've seen which managed to point up two BOL errors.

    Tom