CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Marek Kwiendacz
Marek Kwiendacz

Reputation: 9834

How to return default value from SQL query

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?

Upvotes: 52

Views: 128649

Answers (8)

ANIK ISLAM SHOJIB
ANIK ISLAM SHOJIB

Reputation: 3248

Try this:

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

Upvotes: 8

Sérgio Carvalho
Sérgio Carvalho

Reputation: 1143

If the query is supposed to return at most one row, use a union with the default value then a limit:

SELECT * FROM 
 (
  SELECT Name FROM Users WHERE Id = @UserId`
  UNION
  SELECT 'John Doe' AS Name --Default value
 ) AS subquery
LIMIT 1

Both the query and default can have as many columns as you wish, and you do not need to guarantee they are not null.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

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

Upvotes: 88

Matzi
Matzi

Reputation: 13925

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.

Upvotes: 31

Gulli Meel
Gulli Meel

Reputation: 891

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')

Upvotes: 2

Gulli Meel
Gulli Meel

Reputation: 891

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

Upvotes: 3

Chris Gessler
Chris Gessler

Reputation: 23113

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

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

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)

Upvotes: 4

Related Questions