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?
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
MAX without a GROUP BY or HAVING always returns one row.
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.
COALESCE as this will evaluate the sub query twice
Try this:
SELECT ISNULL(Name, 'John Doe')
FROM Users
WHERE (Id = @UserId)
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)
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
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
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')
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.
