April 9, 2010 at 2:44 am
Hi there,
Just as I thought I was going nuts when fixing a stored procedure I found out what SQL was doing... It smells like a bug to me, but probably it is intended behaviour. I just can't figured out why....
Run this:
DECLARE @myInt INT
SELECT @myInt=3 WHERE 1=0
IF @myInt IS NULL
SELECT '@myInt is null'
ELSE
SELECT @myInt
As expected, you will be prompted with '@myInt is null'
Now run this:
DECLARE @myInt INT
SET @myInt = 8
SELECT @myInt=3 WHERE 1=0
IF @myInt IS NULL
SELECT '@myInt is null'
ELSE
SELECT @myInt
To my astonishment, you will be prompted with 8. It is as if the line 'SELECT @myInt=3 WHERE 1=0' is not executed!
Finally run this:
DECLARE @myInt INT
SET @myInt = 8
SET @myInt= (SELECT 3 WHERE 1=0)
IF @myInt IS NULL
SELECT '@myInt is null'
ELSE
SELECT @myInt
This does give the '@myInt is null' answer I expect.
Why is this?
April 9, 2010 at 2:57 am
I think this is the intended behavior. It is recommended you use set for assignment. Select = will leave the variable untouched if the statement returns no rows.
April 9, 2010 at 6:07 am
This is by design. It is documented in the Remarks section here:
April 9, 2010 at 6:40 am
The first one is doing the same thing. The variable isn't null because of the select returning a null, it's null because it has never been assigned a value. Newly declared variables always return null, till you assign them a value.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2010 at 8:15 am
Thanks for the answers.... I understand now!
April 9, 2010 at 8:39 am
Technically speaking, I believe it is setting the variable once for each row returned.
So, if the query returned 500 rows, the variable would have received 500 different values.
Only the last one would be used in any code following the select statement.
April 9, 2010 at 8:48 am
david_wendelken (4/9/2010)
Technically speaking, I believe it is setting the variable once for each row returned.So, if the query returned 500 rows, the variable would have received 500 different values.
Only the last one would be used in any code following the select statement.
And, in general, the order that values are assigned to the variable is unpredictable, or at least not guaranteed.
April 9, 2010 at 8:58 am
Paul White NZ (4/9/2010)
And, in general, the order that values are assigned to the variable is unpredictable, or at least not guaranteed.
Really? I use it to build strings to use in dynamic sql, such as:
DECLARE @sql nvarchar(max)
SET @sql = ''
SELECT TOP 5 @sql = @sql + '''' + name + ''','
FROM sys.tables
ORDER BY name
SET @sql = LEFT(@sql,LEN(@sql)-1)
SET @sql = 'SELECT * FROM sys.tables WHERE name IN ('+ @sql +')'
EXEC sp_executesql @sql
I've never ran into unpredicted order.
-- Gianluca Sartori
April 9, 2010 at 9:03 am
Gianluca Sartori (4/9/2010)
Paul White NZ (4/9/2010)
And, in general, the order that values are assigned to the variable is unpredictable, or at least not guaranteed.Really? I use it to build strings to use in dynamic sql...I've never ran into unpredicted order.
Not yet 😉
It is not guaranteed, and may break with the next QFE, CU, SP, version...and so on.
FOR XML PATH supports an ORDER BY clause directly, and is faster too!
There is no reason to use the variable-concatenation trick anymore 🙂
April 9, 2010 at 9:08 am
April 9, 2010 at 9:17 am
Assuming that your table names do not contain XML characters that would be entitized, a simpler form is possible:
DECLARE @sql NVARCHAR(MAX);
SET @sql =
STUFF
(
(
SELECT TOP (5)
',' + QUOTENAME(name, CHAR(39)) AS [text()]
FROM sys.tables
ORDER BY name
FOR XML PATH('')
)
, 1, 1, SPACE(0));
SET @sql = N'SELECT * FROM sys.tables WHERE name IN ('+ @sql +');';
PRINT @sql;
April 9, 2010 at 9:18 am
Great tip Paul! Thank you very much!
-- Gianluca Sartori
April 9, 2010 at 9:40 am
Any time, Gianluca 🙂
April 9, 2010 at 1:14 pm
Gianluca Sartori (4/9/2010)
Paul White NZ (4/9/2010)
And, in general, the order that values are assigned to the variable is unpredictable, or at least not guaranteed.Really? I use it to build strings to use in dynamic sql, such as:
DECLARE @sql nvarchar(max)
SET @sql = ''
SELECT TOP 5 @sql = @sql + '''' + name + ''','
FROM sys.tables
ORDER BY name
SET @sql = LEFT(@sql,LEN(@sql)-1)
SET @sql = 'SELECT * FROM sys.tables WHERE name IN ('+ @sql +')'
EXEC sp_executesql @sql
I've never ran into unpredicted order.
With no order by clause, I would agree that the order is indeterminate. That's the SQL standard.
But I did a simple test. I created a simple one column table and indexed that table with an ascending index.
I then selected the column from the table.
I got, in this order, as expected: null, a1, a2, a3.
If I add an order by col1 desc, I get a3, a2, a1, null. Again, all is as expected.
now I do this:
declare @var varchar(1000):
set @var = '';
select @var = @var + ', ' + isnull(col1,'isnull) from the_table;
select @var = @var + ', ' + isnull(col1,'isnull) from the_table order by col1 desc;
I get, as expected, the following:
,isnull, a1, a2, a3, a3, a2, a1, isnull
It definitely appears that the select clause is being evaluated AFTER the order by clause is evaluated. This is not unusual, the TOP clause clearly evaluates after the order by clause too.
Why do you think that's just a quirk of the current version, rather than "all as it should be"?
April 9, 2010 at 10:04 pm
david_wendelken (4/9/2010)
It definitely appears that the select clause is being evaluated AFTER the order by clause is evaluated. This is not unusual, the TOP clause clearly evaluates after the order by clause too. Why do you think that's just a quirk of the current version, rather than "all as it should be"?
Four reasons:
1. Order is never guaranteed without an ORDER BY clause, as you noted.
2. The point at which the variable assignment is evaluated is not guaranteed - it depends on the plan produced, and internal behaviour of the execution engine. It is especially important to note that SQL Server can evaluate a Compute Scalar when it chooses - regardless of its position in the plan. Current internal optimizations allow for the evaluation of a Compute Scalar to be deferred until its value is required by another iterator, for example. This could change.
3. A conversation with one of the SQL Server developers responsible for this part of the engine confirmed that the current code implementation does assign in order - but that is a side-effect of another behaviour, and is not by design. We should never rely on internal implementation details anyway, of course.
4. There is a better way (XML PATH).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply