sqlsql-serversql-server-2008-r2

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?


Solution

  • 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