0

I have a really simple SQL Server query that doesn't return a value. I'd like to see my query return a single row set with a space in it, but I can't seem to get it after a lot of time trying and searching! So, I've got to be doing something wrong and I really feel kind of dumb for having to post such a simple thing, but I can't seem to get it...

Here's the Select:

Select Session from Logins where Session = '123'

In my table the value '123' does not exist under the Session column...

So, I need this simple query to return a space as the value and not return an empty row set.

I've tried ISNULL, COALESCE and the little known IFNULL, all to no effect.

Interestingly, if I remove the where clause from the query it returns multiple rows correctly, none of them as nulls - as SQL Server should do, but when I put in my where clause in I get an empty row set. What I can't understand is why!

All you SQL Server gurus out there, could you please help me to develop a query that returns one column with one row that has a space in it when there is no return set for a where clause?

Thanks in advance,

Jay
  • 548
  • 5
  • 11

1 Answers1

1

Usually, you would want to perform this sort of logic in the client side rather than directly in the query.

But here is one way you can do it in pure SQL by putting your query in a CTE. Not sure if the CTE will get run twice though.

with cte as (
  select session 
    from Logins 
   where session = '123'
)
select session
  from cte
 union all
select ' ' as session
 where not exists (select null from cte)
sstan
  • 35,425
  • 6
  • 48
  • 66
  • Great answer! It worked perfectly! I know that it's not generally a server side type of query although in my case the code is executed on the server and rendered to the client. Kudos! – Jay Dec 05 '15 at 03:16