CODEWITHSUNDEEP
sqlsql-server-2008
Alex J
Alex J

Reputation: 1567

Usage of COALESCE for no rows returned

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.

Upvotes: 8

Views: 11033

Answers (2)

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 1

MatBailie
MatBailie

Reputation: 86715

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')

Upvotes: 13

Related Questions