Interesting Query Results

I was doing a code review and found code that was similar to the code
below. Bad style/practice aside, I was surprised that it ran without
error and did not give "expected" results. Expected meaning five
one-row ouptuts. This is using SQL Server 2000, can anyone tell me if
2005 will act the same or different?
Thanks
declare @.int int
set @.int =0
while @.int<5
begin
declare @.tab table (policy varchar(12), mynum int)
declare @.i int
set @.i=isnull(@.i,0)+1
insert into @.tab
values('test',@.int)
select @.i as I,* from @.tab
set @.int=@.int+1
ENDThe declarations for the table and int variables get "moved" outside the loo
p
by the compiler. I agree the output is not what I would expect either, but I
also think the output is better than what I would expect - i.e. SQL Server i
s
very "smart".
"dott@.accessGeneral.com" wrote:

> I was doing a code review and found code that was similar to the code
> below. Bad style/practice aside, I was surprised that it ran without
> error and did not give "expected" results. Expected meaning five
> one-row ouptuts. This is using SQL Server 2000, can anyone tell me if
> 2005 will act the same or different?
> Thanks
> declare @.int int
> set @.int =0
> while @.int<5
> begin
> declare @.tab table (policy varchar(12), mynum int)
> declare @.i int
> set @.i=isnull(@.i,0)+1
> insert into @.tab
> values('test',@.int)
> select @.i as I,* from @.tab
> set @.int=@.int+1
> END
>|||I think that "the output is better than what I would expect" is VERY
debatable. I would expect an error stating that @.tab and @.i already
exist, just as if I had
declare @.i int
declare @.i int
in code somewhere. But because there is no block level scope the @.tab
and @.i are not freed and redeclared, so by putting the declares inside
the loop should result in an error.|||On 26 Apr 2005 11:35:06 -0700, Otter wrote:

>I think that "the output is better than what I would expect" is VERY
>debatable. I would expect an error stating that @.tab and @.i already
>exist, just as if I had
>declare @.i int
>declare @.i int
>in code somewhere. But because there is no block level scope the @.tab
>and @.i are not freed and redeclared, so by putting the declares inside
>the loop should result in an error.
Hi Otter,
The reason for this is that the declare statements are checked at parse
time, not at execution time. Parsing is just one scan over the code, from
top to bottom, disregarding any flow-of-control statements.
That's why this will work:
IF 1 = 2
BEGIN
DECLARE @.i int
END
SET @.i = 1
SELECT @.i
And this won't
IF 1 = 2
BEGIN
SET @.i = 1
END
DECLARE @.i int
SELECT @.i
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||They don't exactly get moved outside, but the declarations
are not executed statements. They are definitions that define
the context for the rest of the batch when executed. You can
see that they don't really "move" by looking at
while 1=0 begin
select @.i
declare @.i int
end
go
while 1=0 begin
declare @.i int
end
select @.i
The documentation is not very clear on this, but the scope of
variable declarations in SQL Server is from the point of declaration
in the text of the code downward to the end of the batch (first GO).
The execution order of statements in the code is irrelevant. It's the
code as text that matters, as seen here:
goto a
select @.x
a: declare @.x int
The environment (i.e., the variable declarations and values) are not
passed into subprograms like stored procedures or into queries
called with EXEC (<string> ), unless passed explicitly through an
available mechanism, like stored procedure parameters.
The behavior in T-SQL is not too far from the behavior in most
programming languages, except that scope does not end with
the end of a block like BEGIN .. END, only with the end of a
batch.
if 1=1 begin
declare @.i int
set @.i = 1
end else begin
declare @.j int
set @.j = 1
end
select @.i, @.j
If I remember my C++, for example, the variable i is not
redeclared with each loop iteration below, and that is the same
behavior we see in T-SQL. But i is not in scope below the
loop, and that is not the same behavior as in T-SQL. In
T-SQL there is no option to initialize a variable at declaration
like I'm doing here. All in all, there's no good reason I can think
of to declare a T-SQL variable inside a loop unless it puts
the variable closer to its use and readers will not think it
has C-like block scope.
[untested]
x = 10;
while --x {
int i = 0;
i += x;
cout << i
}
Steve Kass
Drew University
KH wrote:
>The declarations for the table and int variables get "moved" outside the lo
op
>by the compiler. I agree the output is not what I would expect either, but
I
>also think the output is better than what I would expect - i.e. SQL Server
is
>very "smart".
>
>"dott@.accessGeneral.com" wrote:
>
>sql