Coalesce

Hi.

I have a piece of a store procedure I don't quite understand, as follows:

SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

What exactly is the COALESCE function doing here with the parameter?Returns the first nonnull expression among its arguments.|||Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.|||Originally posted by SQLSlammer
Ok,

What does that mean? it returns the first parameter that is null or it returns the first record that is null.

declare @.id int,@.id2 int
set @.id=null
set @.id2=1
select COALESCE(@.id,@.id2)

It returns the first parameter that is not null...

WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

In your query COALESCE(@.DealReference,'%')) means that if @.DealReference is null then COALESCE returns '%' and finally it is LIKE '%'.|||coalesce returns the first non-null value in its list

in your particular example, it's a sloppy stored proc

if the parameter @.DealReference is null, then the sql statement will actually look like this:

... where d.DealReference LIKE (COALESCE(null,'%'))

but in that case, since it's a stored proc, it should not generate a WHERE clause at all

furthermore, since it's a LIKE condition, the @.DealReference has better include the wildcard characters, otherwise it will function as an equality

again, the stored proc should tailor the sql specifically|||Hi,

Thanks for that,
so basically it will return all records if the parameter is null, what would be a beter way of handling a null parameter?|||what would be a better way of handling a null parameter?

don't generate the WHERE clause

that, too, will return all records|||Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much|||Originally posted by SQLSlammer
Hi,

Ok, I should explain a little more, there is lot more to this sp than I have shown, there are 7 parameters for filtering and I really don't want it to return all records cos that would be over 7 million records into the temp table which is pointless:

The full SP is attached and yes I know its not very good (I did not write this).

Any advice would be appreciated very much

Well, there is bad with using coalesce in this proc. I guess you could change a little logic in your proc:

You are inserting more records in table @.Deals than late you selecting from it because in select you are using more filters then for insert. You could use all filters during inserting - it will be more complicated query but it is worth to try.

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)\par
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId\par
FROM utDeal d WITH (NOLOCK)\par
\tab INNER JOIN utContactIllustration ci WITH (NOLOCK)\par
\tab\tab ON ci.IllustrationId = d.IllustrationId\par
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%')) \par

SELECT DISTINCT * FROM @.deals \par
WHERE \tab\par
\tab DealReference LIKE (COALESCE(@.DealReference,'%')) \par
AND\par
\tab UserName LIKE (COALESCE(@.UserName, '%'))\par
AND\par
\tab LastAction LIKE (COALESCE(@.ActionType, '%'))\par
AND\par
\tab PostCode LIKE (COALESCE(@.PostCode, '%'))\par
AND\par
\tab CurrentStage LIKE (COALESCE(@.Stage, '%'))\par
AND\par
\tab Surname LIKE (COALESCE(@.Surname, '%'))\par
AND\par
\tab Telephone LIKE (COALESCE(@.Telephone, '%'))\par
ORDER BY DealId\par|||you know your data better than i do, but the relevant statement in your proc is

INSERT @.Deals (DealReference, DealId, IllustrationId, ContactId)
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@.DealReference,'%'))

so if @.DealReference is null, it will bring back all rows that satisfy that join, whether that's 7 million, i don't know, but it could be