Using COALESCE without returning rows

Question

I have used COALESCE many times, but I guess I never came across this particular situation. Suppose there is only one line on @ param1.

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

In this case, if column1 is null, "default" is selected when the sql statement actually returns a row. What to do if there are no matching entries for @ param1.

Suppose I want to always have a default value for @test. Is this right or is there another way?

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

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

I suppose that I can also check if @test is NULL after that and assign a default value.

Answer 1

You have already effectively mentioned the answer ... Use COALESCE after / outside of SELECT, since otherwise you never assign a value (which is different than 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 can also just give the variable a default value when declaring. If no rows are returned by the query, no assignment will be performed.

 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