SELECT @local_variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Honza Mensdorff » SELECT @local_variable

16 posts, Page 2 of 2

««1 2

SELECT @local_variable

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Monday, December 14, 2009 10:27 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #833984

Richard Gibbins

Posted Monday, December 14, 2009 1:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:36 PM
Points: 145, Visits: 164

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.

Post #834118

vk-kirov

Posted Monday, December 14, 2009 1:53 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:50 PM
Points: 3,208, Visits: 4,178

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.

Post #834138

Richard Gibbins

Posted Monday, December 14, 2009 1:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:36 PM
Points: 145, Visits: 164

Ah! Good catch on Oracle vs Sql Server; I didn't realize it was that obvious Smile

.

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

Richard

Post #834139

honza.mf

Posted Monday, December 14, 2009 2:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 1:11 AM
Points: 1,114, Visits: 1,209

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.

Post #834152

Peter Trast

Posted Saturday, March 20, 2010 2:07 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654

I got lucky and guessed the one with all of the text... :)

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

Post #886959

« Prev Topic | Next Topic »

16 posts, Page 2 of 2

««1 2

Permissions