CODEWITHSUNDEEP
sql
jl6
jl6

Reputation: 6394

Use of coalesce function when no rows returned

Here's a post that uses the following statement:

CREATE FUNCTION ValidateExclVehicle_check (
 @vin           CHAR(17),
 @vehicle_type  CHAR(3)
)
RETURNS TINYINT
AS
-- Exists     = 1
-- Not Exists = Null, 0 substituted
RETURN ( 
SELECT COALESCE(1, 0)
 FROM  Vehicle
 WHERE vin          = @vin
 AND   vehicle_type = @vehicle_type 
)

I don't understand why the expression COALESCE(1,0) would ever return anything other than 1, except when the where clause returns no rows, in which case the whole statement returns no rows and the value of the coalesce expression is irrelevant. I don't see the "0 substituted".

Is there something going on that I'm missing?

Upvotes: 0

Views: 839

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I agree with you. That statement should be returning 1 or NULL/no rows. This might be a database specific phenomenon, assuming that you do see it return 0.

I think the intention is:

return (select (case when count(*) = 0 then 0 else 1 end)
        from Vehicle
        where vin = @vin and vehicle_type = @vehicle_type
       )

Upvotes: 2

Related Questions