up vote 11 down vote favorite
2

Is there any easy way to return single scalar or default value if query doesn't return any row?

At this moment I have something like this code example:

IF (EXISTS (SELECT * FROM Users WHERE Id = @UserId))  
    SELECT Name FROM Users WHERE Id = @UserId  
ELSE
    --default value
    SELECT 'John Doe'

How to do that in better way without using IF-ELSE?

share|improve this question
create a stored procedure for any database programming with additional logic. – Uğur Gümüşhan Jul 22 '12 at 23:00

6 Answers

up vote 21 down vote accepted

Assuming the name is not nullable and that Id is unique so can match at most one row.

 SELECT ISNULL(MAX(Name),'John Doe')
 FROM Users 
 WHERE Id = @UserId
share|improve this answer
Simplest version so far and +1 on mentioning the unique requirement. – Lieven Keersmaekers Jul 16 '12 at 11:53
Will this work when no rows are returned?? – Chris Gessler Jul 16 '12 at 11:56
@ChrisGessler - Yes. A MAX without a GROUP BY or HAVING always returns one row. – Martin Smith Jul 16 '12 at 11:56
1  
SWEET! +1 for you. Nice trick, I'll have to remember this. I already know of several spots I can use it. – Chris Gessler Jul 16 '12 at 11:59
up vote 12 down vote

Try ISNULL or COALESCE:

SELECT ISNULL((SELECT TOP 1 Name FROM Users WHERE Id = @UserId), 'John Doe')

The inner select will return nothing if no user exist with this id, the isnull will solve this case.

share|improve this answer
+1 but there's a small risk that the select returns more than one row in wich case this won't work. – Lieven Keersmaekers Jul 16 '12 at 11:51
4  
Don't use COALESCE as this will evaluate the sub query twice – Martin Smith Jul 16 '12 at 11:52
Sweet little one liner, but you'll need to add top 1 (just in case) – Chris Gessler Jul 16 '12 at 11:52
up vote 2 down vote

You can drop the if statement using following construct but that doesn't necessarely mean it is better.

SELECT Name FROM Users WHERE Id = @UserId UNION ALL 
SELECT 'John Doe' WHERE NOT EXISTS (SELECT Name FROM Users WHERE  Id = @UserId)
share|improve this answer
up vote 1 down vote

Try isnull

SELECT IsNULL(Name, 'John Doe')  FROM Users WHERE Id = @UserId

Edit:

drop table users
go
create table users
(id int,name varchar(20))
go
insert into users select 1,'1'
 go
declare @userid int
set @userid = 1
select isnull(username.username, 'John Doe')
from (select @userid as userid) userid
outer apply (SELECT  name as username FROM Users WHERE Id = userid.userid ) username
--outer apply (SELECT  name as username FROM Users WHERE Id = @userid ) username
share|improve this answer
1  
This requires at least 1 record with a null value for Name – Chris Gessler Jul 16 '12 at 11:44
My bad. I thought he is getting null value.. – Gulli Meel Jul 16 '12 at 12:12
You can try this as well – Gulli Meel Jul 16 '12 at 12:32
select isnull(username.username,'your vaklue') as username from (select null userid) a outer apply ( select username from usertable where userid = a.userid) username – Gulli Meel Jul 16 '12 at 12:33
Update your answer, I hadn't though of outer apply. – Chris Gessler Jul 16 '12 at 12:34
show 1 more comment
up vote 0 down vote

I suppose you could use @@ROWCOUNT to see if any will be returned.

SELECT Name FROM Users WHERE Id = @UserId  
if(@@ROWCOUNT = 0)
  SELECT 'John Doe'

You could also use a variable if you're expecting one row.

declare @name varchar(100)
set @name = (select top 1 name from users where id = @userId)
if(@name is null) set @name = 'John Doe'
select @name
share|improve this answer
up vote 0 down vote

I would suggest that the best way to do is that first declare @name . Then set this value based on user id and then if @name is null show default name otherwise show name... That method would be as efficient as any other method and will be more readable.for other methods any other user to have think a lot to know what is going on unless there is nice comment.

declare @userid int
set @userid = 1
select isnull(
               (select name from users where id = @userid),
               'John Doe'
               )
 go
--My preffered would be this one..
declare @name varchar(20),@userid int
set @userid = 1
select  @name =  name from users where id = @userid
select isnull(@name,'John Doe')
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.