SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Honza Mensdorff / SELECT @local_variable / Latest Posts

RE: SELECT @local_variable

Peter Trast — Sat, 20 Mar 2010 14:07:00 GMT

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??

RE: SELECT @local_variable

honza.mf — Mon, 14 Dec 2009 14:19:36 GMT

[quote][b]vk-kirov (12/14/2009)[/b][hr]In SQL Server, an empty string and a NULL value are not the same.[/quote][quote]As far as I know, NULLs and empty strings are equal in Oracle. But in SQL Server they are not.[/quote]To distinguish NULLs and empty strings or to find padding spaces I use [code="sql"]select '«' + @txt + '»'[/code] 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.

RE: SELECT @local_variable

Richard Gibbins — Mon, 14 Dec 2009 13:58:49 GMT

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

RE: SELECT @local_variable

vk-kirov — Mon, 14 Dec 2009 13:53:23 GMT

[quote][b]Richard Gibbins (12/14/2009)[/b][hr]To see what happened when there is a null value, I tried:[code="sql"]declare @txt varchar(255)set @txt = 'Question'insert xxx values ('')select @txt = isnull (a, '?') from xxx order by i[/code]and got a null string and not "?" as I was expecting.[/quote]In SQL Server, an empty string and a NULL value are not the same.It can be shown by this:[code="sql"]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 iselect @txt -- the result is '' (empty string)insert #xxx values (null)select @txt = isnull (a, '?') from #xxx order by iselect @txt -- the result is '?'select @txt = a from #xxx order by iselect @txt -- the result is NULLdrop table #xxx[/code]and this:[code="sql"]declare @var varchar(255)set @var = ''if @var is null select 'NULL' else select 'NOT NULL' -- the result is 'NOT NULL'set @var = nullif @var is null select 'NULL' else select 'NOT NULL' -- the result is 'NULL'[/code]As far as I know, NULLs and empty strings are equal in Oracle. But in SQL Server they are not.

RE: SELECT @local_variable

Richard Gibbins — Mon, 14 Dec 2009 13:17:00 GMT

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:[code="sql"]declare @txt varchar(255)set @txt = 'Question'insert xxx values ('')select @txt = isnull (a, '?') from xxx order by i[/code]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.

RE: SELECT @local_variable

SQLRNNR — Mon, 14 Dec 2009 10:27:22 GMT

[quote][b]Christian Buettner-167247 (12/14/2009)[/b][hr]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.[code]SET NOCOUNT ONcreate table #xxx (i int identity, a varchar(3))godeclare @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 1select @txtDROP TABLE #xxx[/code]Result:[quote]Question-the[/quote]Looks like only the first or last (depending on DESC or ASC sort) row is used in the end result.Is that expected behaviour?[/quote]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.

RE: SELECT @local_variable

honza.mf — Mon, 14 Dec 2009 05:00:55 GMT

[quote][b]Christian Buettner-167247 (12/14/2009)[/b][hr]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 [quote]Is it OK to order on missing column?[/quote]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...[/quote]I don't know what is going on behind the scenes. Sometimes I do some type of [i]mental reverse engineering[/i], just trying "How do I programme this".This additive assignment to local variables I feel to be [i]an implicit cursor[/i]. 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.

RE: SELECT @local_variable

honza.mf — Mon, 14 Dec 2009 04:53:30 GMT

[quote][b]Toreador (12/14/2009)[/b][hr]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 :-)[/quote]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 :-PI will think about it, if I will write some other QotD

RE: SELECT @local_variable

Toreador — Mon, 14 Dec 2009 03:53:34 GMT

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 ithen 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.htmBut if it actually does it once per row then it makes sense thatselect @txt = @txt + ' ' + a from xxx order by ishould end up with a concatentation of all the values.

RE: SELECT @local_variable

vk-kirov — Mon, 14 Dec 2009 03:30:03 GMT

[quote][b]Christian Buettner-167247 (12/14/2009)[/b][hr]Same code as before, but positioned order by (1 instead of i)[code]select @txt = @txt + '-' + a from #xxx order by 1[/code][/quote]Not the same code. Your code is equal to [code]select @txt = @txt + '-' + a from #xxx order by @txt + '-' + a[/code].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 :-)

RE: SELECT @local_variable

Christian Buettner-167247 — Mon, 14 Dec 2009 03:04:08 GMT

[quote][b]honza.mf (12/14/2009)[/b][hr][quote][b]Christian Buettner-167247 (12/14/2009)[/b][hr]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.[/quote]As said in explanation:[quote]The second construct is not documented, but works this same way on MS SQL Server 2000, 2005, and 2008.[/quote]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?[/quote]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 [quote]Is it OK to order on missing column?[/quote]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...

RE: SELECT @local_variable

honza.mf — Mon, 14 Dec 2009 02:49:05 GMT

[quote][b]Christian Buettner-167247 (12/14/2009)[/b][hr]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.[/quote]As said in explanation:[quote]The second construct is not documented, but works this same way on MS SQL Server 2000, 2005, and 2008.[/quote]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?

RE: SELECT @local_variable

Christian Buettner-167247 — Mon, 14 Dec 2009 02:33:03 GMT

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.[code]SET NOCOUNT ONcreate table #xxx (i int identity, a varchar(3))godeclare @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 1select @txtDROP TABLE #xxx[/code]Result:[quote]Question-the[/quote]Looks like only the first or last (depending on DESC or ASC sort) row is used in the end result.Is that expected behaviour?

RE: SELECT @local_variable

vk-kirov — Mon, 14 Dec 2009 01:59:45 GMT

[quote][b]Carlo Romagnano (12/14/2009)[/b][hr]That isn't true.Count() return 0[/quote]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.

RE: SELECT @local_variable

Carlo Romagnano — Mon, 14 Dec 2009 01:17:03 GMT

[quote]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. [/quote]That isn't true.Count() return 0create table xxx (i int identity, a varchar(3))godeclare @txt varchar(255)set @txt = 'Question'select @txt = count(1) from xxx order by 1insert xxx values ('of')insert xxx values ('the')insert xxx values ('day')select @txt = @txt + ' ' + a from xxx order by iselect @txt

SELECT @local_variable

honza.mf — Sat, 12 Dec 2009 11:23:54 GMT

Comments posted to this topic are about the item [B]SELECT @local_variable[/B]