SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT @local_variable


SELECT @local_variable

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67423 Visits: 18570
Christian Buettner-167247 (12/14/2009)
Hm, is someone able to explain this?

Same code as before, but positioned order by (1 instead of i) and "-" as separator for better readability.
SET NOCOUNT ON
create table #xxx (i int identity, a varchar(3))
go
declare @txt varchar(255)
set @txt = 'Question' select @txt = isnull (a, '?') from #xxx order by i insert #xxx values ('of')
insert #xxx values ('the')
insert #xxx values ('day') select @txt = @txt + '-' + a from #xxx order by 1
select @txt
DROP TABLE #xxx



Result:

Question-the


Looks like only the first or last (depending on DESC or ASC sort) row is used in the end result.
Is that expected behaviour?


Interesting, I see the same results when using the "order by 1" in the variable assignment.

In SSMS 2008, the query will run but it throws a warning prior to execution.

"The Order By position number 1 is out of range of the number of items in the select list."

I believe the results being returned are due to the out of range error.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Richard Gibbins
Richard Gibbins
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 209
I find the variable assignment rather interesting in that it seems to occur at a curious point. To see what happened when there is a null value, I tried:


declare @txt varchar(255)
set @txt = 'Question'
insert xxx values ('')
select @txt = isnull (a, '?') from xxx order by i



and got a null string and not "?" as I was expecting.

Does you know why? It seems to me that the variable assignment occurs at a rather odd place in the execution of the statement.
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4344 Visits: 4408
Richard Gibbins (12/14/2009)
To see what happened when there is a null value, I tried:

declare @txt varchar(255)
set @txt = 'Question'
insert xxx values ('')
select @txt = isnull (a, '?') from xxx order by i


and got a null string and not "?" as I was expecting.

In SQL Server, an empty string and a NULL value are not the same.

It can be shown by this:
create table #xxx (i int identity, a varchar(3))
declare @txt varchar(255)
set @txt = 'Question'
insert #xxx values ('')
select @txt = isnull (a, '?') from #xxx order by i
select @txt -- the result is '' (empty string)
insert #xxx values (null)
select @txt = isnull (a, '?') from #xxx order by i
select @txt -- the result is '?'
select @txt = a from #xxx order by i
select @txt -- the result is NULL
drop table #xxx



and this:
declare @var varchar(255)
set @var = ''
if @var is null select 'NULL' else select 'NOT NULL' -- the result is 'NOT NULL'
set @var = null
if @var is null select 'NULL' else select 'NOT NULL' -- the result is 'NULL'



As far as I know, NULLs and empty strings are equal in Oracle. But in SQL Server they are not.
Richard Gibbins
Richard Gibbins
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 209
Ah! Good catch on Oracle vs Sql Server; I didn't realize it was that obvious:-).

Thanks for the explanation; it makes more sense than what I was imagining.

Richard
honza.mf
honza.mf
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2569 Visits: 1323
vk-kirov (12/14/2009)

In SQL Server, an empty string and a NULL value are not the same.

As far as I know, NULLs and empty strings are equal in Oracle. But in SQL Server they are not.


To distinguish NULLs and empty strings or to find padding spaces I use
select '«' + @txt + '»'

The difference is visible at the first look. You can use some other type of quotes or brackets if you like.

Sorry to oraclists, I am used to MS SQL Server.



See, understand, learn, try, use efficient
© Dr.Plch
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1072 Visits: 655
I got lucky and guessed the one with all of the text... Smile

I am not seeing why the concatenation of the inserts occurs... can someone break this down further??

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search