Today, customer feedback found that there is a bug in MS SQL, the problem is quite large, so issued to make a mark:
Open the SQL query window and paste in the following code:
DECLARE @i intset @i = 1while @i<5--loop execution 4 times begin DECLARE @aa varchar (1) --Define variable print convert (varchar (1), @i) + '--' +is Null (@aa, ") --Print variable Select @aa = ' 0 ' where 1>2 --to determine if 1 is greater than 2 is assigned to the variable, the argument is not set so the variable is still not assigned if @aa is null -- If the variable does not fetch a value, the variable is assigned a value of 1 set @aa = ' 1 ' Set @i = @i + 1 --Loop End
run complete, find out what situation:
The
2--1
3--1
4--1 The variables defined inside the loop, the first time the variable is printed is really normal, because there is no assignment so it is empty,But after a few loops, it turns out to be 1.What's the situation? The system actually took the value of the last loop!
SQL Server defines the variable in the loop, and if the variable is assigned at the end of the loop, it will cause the next loop to assign the value to the defined variable .Finally, there is no way to assign a value to a variable before performing a related operation:
DECLARE @i intset @i = 1while @i<5begin declare @aa varchar (1) Set @aa = ' Print convert (varchar (1), @i) + '--' +isnull (@a A, ') Select @aa = ' 0 ' where 1>2 if @aa is null set @aa = ' 1 ' Set @i = @i + 1end
Then run the following results:
The
2--
3--
4--
Alas, this question, Microsoft has a little water
Bug in SQL Server