Usage of COALESCE for no rows returned

Question

I have used COALESCE numerous times but I suppose I never encountered this particular situation. Assume there is only one row per @param1.

select @test = COALESCE (column1, 'default') FROM Table1 WHERE column3 = @param1

In this scenario, if the value of column1 is null, 'default' is selected when the sql statement actually returns a row. What if there are no matching records for @param1.

Lets say I want to always have a default value for @test. Is the following correct or is there another way?

select @test = COALESCE( (select column1 FROM Table1 WHERE column3 = @param1), 'default').

I presumed that, select @test = COALESCE (column1, 'default') FROM Table1 WHERE column3 = @param1, will contain 'default' even if it did not return a row. Nope.

I suppose I can also check if @test is NULL afterwards and assign a default value as well.

Answer 1

You already effectively mentioned the answer... Use COALESCE after/outside the SELECT, as otherwise you never actually assign a value (which is different from assigning a NULL value)...

SELECT @test = NULL
SELECT @test = column1 FROM Table1 WHERE column3 = @param1
SELECT @test = COALESCE(@test, 'default')

Or simply...

SELECT @test = COALESCE((SELECT column1 FROM Table1 WHERE column3 = @param1), 'default')

Answer 2

You could also just give the variable the default value at declaration. If no rows are returned by the query no assignment will be made.

DECLARE @test VARCHAR(10) = 'default'

SELECT  @test = COALESCE(column1, @test) /*Might not need COALESCE 
                                           if column1 is not nullable*/
FROM    Table1
WHERE   column3 = @param1 

SELECT  @test