Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT @local_variable


SELECT @local_variable

Author
Message
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1323
Comments posted to this topic are about the item SELECT @local_variable



See, understand, learn, try, use efficient
© Dr.Plch
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3834 Visits: 3276
If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

That isn't true.
Count() return 0

create table xxx (i int identity, a varchar(3))
go
declare @txt varchar(255)set @txt = 'Question'
select @txt = count(1) from xxx order by 1
insert xxx values ('of')insert xxx values ('the')insert xxx values ('day')
select @txt = @txt + ' ' + a from xxx order by i
select @txt

I run on tuttopodismo
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3540 Visits: 4408
Carlo Romagnano (12/14/2009)

That isn't true.
Count() return 0

This is not true that "That isn't true" :-)
"SELECT COUNT" statement from your example returns a zero as a result, so the result contains one row. Thus the condition "the SELECT statement returns no rows" fails and the variable changes its value.
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3071 Visits: 3889
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?

Best Regards,

Chris Büttner
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1323
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.


As said in explanation:
The second construct is not documented, but works this same way on MS SQL Server 2000, 2005, and 2008.

I don't know how the undocumented construct works. But there are another questions: What is the first column in your select? Is it OK to order on missing column?



See, understand, learn, try, use efficient
© Dr.Plch
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3071 Visits: 3889
honza.mf (12/14/2009)
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.


As said in explanation:
The second construct is not documented, but works this same way on MS SQL Server 2000, 2005, and 2008.

I don't know how the undocumented construct works. But there are another questions: What is the first column in your select? Is it OK to order on missing column?

Hi Honza,

I know it's not documented - I just would like to know if someone knows what is going on behind the scenes. I am just curious. My question was not meant to be a challenge for your QotD.

But with regards to your question
Is it OK to order on missing column?

It "might" be OK, since SQL Server is not generating an error.
But it might as well be not OK (to which I tend more)
But take the "unsupported" SET statement from your QotD - is it OK to use it as it is? From a SQL standpoint it should not be valid, but obviously it seems to work and you can do some useful stuff with it.

Now don't ask me for what you could use the positioned order by in my example - actually i just misread your example and exchanged the I with a 1 accidentially. Took me a while to figure out what I had done wrong...

Best Regards,

Chris Büttner
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3540 Visits: 4408
Christian Buettner-167247 (12/14/2009)

Same code as before, but positioned order by (1 instead of i)
select @txt = @txt + '-' + a from #xxx order by 1



Not the same code. Your code is equal to
select @txt = @txt + '-' + a from #xxx order by @txt + '-' + a

.
Looks like SQL Server doesn't use recursive assignment in this case. SQL Server cannot change the value of a variable and, at the same time, sort data using this volatile variable.
When "order by i" is used, SQL Server can assign values to the variable recursively.
This is just my guess :-)
Toreador
Toreador
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2519 Visits: 8064
I guessed rigtht, but only because I knew it had to begin with 'Question' and there was only one answer that did that. If there'd been an option for 'Question day' then I'd have gone for that and got it wrong :-)

Thinking about it, if you do
select @txt = ' ' + a from xxx order by i

then the variable ends up with the value 'day'. This is presumably because it applies the assignment to each and every row, so it ends up with the value of the last one. I'd assumed that it was more 'intelligent' than this and just did the assignement once, for the last row. That is what is implied by BOL which says "If the SELECT statement returns more than one value, the variable is assigned the last value that is returned."
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/8e1a9387-2c5d-4e51-a1fd-a2a95f026d6f.htm

But if it actually does it once per row then it makes sense that
select @txt = @txt + ' ' + a from xxx order by i
should end up with a concatentation of all the values.
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1323
Toreador (12/14/2009)
I guessed rigtht, but only because I knew it had to begin with 'Question' and there was only one answer that did that. If there'd been an option for 'Question day' then I'd have gone for that and got it wrong :-)


I wanted to mix two things and not to make it too complicated.
There was a very similar QotD few weeks ago. I was afraid this one will become much more simple. And it was taken 2 points :-P

I will think about it, if I will write some other QotD



See, understand, learn, try, use efficient
© Dr.Plch
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1323
Christian Buettner-167247 (12/14/2009)

Hi Honza,

I know it's not documented - I just would like to know if someone knows what is going on behind the scenes. I am just curious. My question was not meant to be a challenge for your QotD.

But with regards to your question
Is it OK to order on missing column?

It "might" be OK, since SQL Server is not generating an error.
But it might as well be not OK (to which I tend more)
But take the "unsupported" SET statement from your QotD - is it OK to use it as it is? From a SQL standpoint it should not be valid, but obviously it seems to work and you can do some useful stuff with it.

Now don't ask me for what you could use the positioned order by in my example - actually i just misread your example and exchanged the I with a 1 accidentially. Took me a while to figure out what I had done wrong...


I don't know what is going on behind the scenes.
Sometimes I do some type of mental reverse engineering, just trying "How do I programme this".
This additive assignment to local variables I feel to be an implicit cursor. I suppose the server creates something like a cursor and makes an assignment in it. I haven't compared yet the efficiency of these two variants (single select assignment and cursor). At least it is less coding.



See, understand, learn, try, use efficient
© Dr.Plch
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