How to return Null or value when nothing is returned from Query

Question

How to return Null or value when nothing is returned from Query

Noc234234

Old Hand

Points: 335
More actions
October 10, 2011 at 12:21 pm

#246275

I have a simple query:
SELECT name FROM atable WHERE a = 1
I want it to return Null if it finds nothing, but it just returns an empty set.
Is it possible to do this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Well by definition you can't return anything if there are no records. 😉 You would have to force the query to always return a resultset.

Something like this:

select top 1 name from

(

SELECT name, 1 as SortOrder FROM atable WHERE a = 1

union select null, 2

) myTable

order by SortOrder

So you can force the issue but it seems this type of thing is more suited to the front end instead of trying to make sql return data when there is no data to return. This is forcing the square peg into the round hole.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tyson.price SSCrazy Points: 2093 More actions · Answer 2

tnocella (10/10/2011)
I have a simple query:
SELECT name FROM atable WHERE a = 1
I want it to return Null if it finds nothing, but it just returns an empty set.
Is it possible to do this?

Maybe this:

if exists(SELECT name FROM atable WHERE a = 1)

SELECT name FROM atable WHERE a = 1

else

select null -- or 'Not found'

vliet SSCommitted Points: 1998 More actions · Answer 3

When you know there's only zero or one result row:

SELECT MAX(name) FROM atable WHERE a = 1

But maybe that's too simple ...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Never miss a (nearly) free opportunity to gather some information:

SELECT Result = NULLIF(COUNT(*),0) FROM atable

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vikingDBA SSCertifiable Points: 5356 More actions · Answer 5

I use the ISNULL() function for things of this nature.

SET @someval = ISNULL((SELECT Name FROM SOMETABLE WHERE ID=3),'')

OR

SELECT @someval = ISNULL(Name,'') FROM SOMETABLE WHERE ID=3

if the result is null, will replace it with '' (blank string).

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

SET @someval = ISNULL((SELECT Name FROM SOMETABLE WHERE ID=3),'')

OR

SELECT @someval = ISNULL(Name,'') FROM SOMETABLE WHERE ID=3

This assumes that the subquery returns no more than one row, which might not be valid.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

vikingDBA SSCertifiable Points: 5356 More actions · Answer 7

True, assuming that ID = 3 can only occur on one record.