CODEWITHSUNDEEP
sqlsql-server
Imad
Imad

Reputation: 7490

Select default if no rows return

SELECT Column1 
FROM Table1 
WHERE PKColumn = SomeValue

I am selecting just one column, my query will return only 0 or 1 row for sure. I want to select some default values like Some Default if no row returned otherwise the returned value.

I tried something like 

SELECT CASE WHEN COUNT(1) = 1 THEN Column1 ELSE 'Some Default' END AS Column1 
FROM Table1 
WHERE PKColumn = SomeValue  
GROUP BY Column1

But it doesn't work.

Is there any way to do it in single SQL statement?

Upvotes: 0

Views: 561

Answers (4)

Cer
Cer

Reputation: 258

You can use COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

SELECT COALESCE((SELECT TOP 1 Column1 FROM Table1 WHERE PK = SomeValue), 'DefaultValue')

or like this:

DECLARE @ReturnValue INT = 3 -- Default value
SELECT TOP 1 @ReturnValue = Column1 FROM Table1 WHERE PK = SomeValue
SELECT @ReturnValue

Upvotes: 1

Koo SengSeng
Koo SengSeng

Reputation: 953

Perhaps can try using LEN instead of COUNT to check if Column1 has value?

SELECT CASE WHEN LEN(Column1) > 0 THEN Column1 ELSE 'Some Default' END AS Column1 
FROM Table1 
WHERE PKColumn = SomeValue  
GROUP BY Column1

Upvotes: 0

shA.t
shA.t

Reputation: 16958

I think you can use a query like this:

SELECT TOP(1) 
    CASE WHEN EXISTS(SELECT 1 FROM t WHERE PKColumn = SomeValue) THEN Column1 
         ELSE 'Some Default' END AS Column1
FROM t;

Or using EXISTS with UNION:

SELECT 'Some Default' As Column1
WHERE NOT EXISTS(SELECT 1 FROM t WHERE PKColumn = SomeValue)
UNION ALL
SELECT Column1
FROM t
WHERE PKColumn = SomeValue;

Upvotes: 2

Juozas
Juozas

Reputation: 935

SELECT 
     [Column1] = ISNULL([t2].[Column1], 'Some Default')
FROM 
    [Table1]    AS  [t1]
OUTER APPLY
    (
        SELECT
             [Column1]
        FROM
            [Table1]
        WHERE
                [PKColumn] = [t1].[PKColumn]              
    )           AS  [t2]         
WHERE 
        [t1].[PKColumn] = 'SomeValue';

Upvotes: 0

Related Questions