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.

Carlo Romagnano (12/14/2009)

---

That isn't true.
Count() return 0

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

Question-the

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.

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

honza.mf (12/14/2009)

---

As said in explanation:

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?

Is it OK to order on missing column?

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

select @txt = @txt + '-' + a from #xxx order by @txt + '-' + a

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

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