Results 1 to 10 of 10

Thread: Coalesce

  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: 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?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Coalesce

    Returns the first nonnull expression among its arguments.

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Ok,

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

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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 '%'.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what would be a better way of handling a null parameter?

    don't generate the WHERE clause

    that, too, will return all records
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    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
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •