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: How to do that in better way without using IF-ELSE? Answer Assuming the name is not nullable and that Id is unique so can match at most one row.

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'

​x
 
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?

Answer

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

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

Advertisement

Answer