locked
Coalesce() RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

       Could someone explain what is the meaning of this line. This is part of my stored procedure

    Create procedure sp
    (
    @HeaderId int,  

    @LoanNumber

    NVARCHAR(30)=NULL

     

     


    )
    SELECT Id

     

    FROM ResponseView hrv

     

    WHERE hrv.Id = @HeaderId

     

    AND hrv.ServicerLoanNumber LIKE COALESCE(@LoanNumber,hrv.ServicerLoanNumber)

    What is the meaning of this line  COALESCE(@LoanNumber,hrv.ServicerLoanNumber)  ?
    Does it mean that get the value for hrv.ServicerLoanNumber from @LoanNumber  ?

    Does the ordr really matter ..I mean can I   write like this  COALESCE(hrv.ServicerLoanNumber,@LoanNumber)     ? What does it really mean?


    Any help would be gtreatly appreciated

    Thanks

    Wednesday, February 10, 2010 9:59 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    This means that "hrv.ServicerLoanNumber" must be like the @LoanNumber variable or that @loanNumber must be null.  If @loanNumber is null the value of the AND condtion is TRUE; If @loanNumber is not null then the "hrv.ServicerLoanNumber" must be like the @loanNumber variable (or parameter).

    This kind of code typically doesn't perform well for the cases in which @LoanNumber is passed and will result in a table scan.  The table scan can sometimes be avoided by using IF / ELSE rather than the coalesce.
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:48 AM
    Wednesday, February 10, 2010 10:01 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    COALESCE(@LoanNumber,hrv.ServicerLoanNumber)

    means...

    CASE WHEN @LoanNumber IS NULL THEN hrv.ServicerLoanNumber ELSE @LoanNumber END

    In other words...

    if @LoanNumber has a null value, use hrv.ServicerLoanNumber... otherwise, use @LoanNumber as is.

    --Brad (My Blog)
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:46 AM
    Wednesday, February 10, 2010 10:02 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Hi there,

    Just to add a different approach.

    COALLESCE returns the first not null value, it can be used with a variable number of arguments and it will return the value of the first not null argument. Values are evaluated from left to right.

    José Cruz
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:48 AM
    Wednesday, February 10, 2010 10:44 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    This means that "hrv.ServicerLoanNumber" must be like the @LoanNumber variable or that @loanNumber must be null.  If @loanNumber is null the value of the AND condtion is TRUE; If @loanNumber is not null then the "hrv.ServicerLoanNumber" must be like the @loanNumber variable (or parameter).

    This kind of code typically doesn't perform well for the cases in which @LoanNumber is passed and will result in a table scan.  The table scan can sometimes be avoided by using IF / ELSE rather than the coalesce.
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:48 AM
    Wednesday, February 10, 2010 10:01 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    COALESCE(@LoanNumber,hrv.ServicerLoanNumber)

    means...

    CASE WHEN @LoanNumber IS NULL THEN hrv.ServicerLoanNumber ELSE @LoanNumber END

    In other words...

    if @LoanNumber has a null value, use hrv.ServicerLoanNumber... otherwise, use @LoanNumber as is.

    --Brad (My Blog)
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:46 AM
    Wednesday, February 10, 2010 10:02 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Hi there,

    Just to add a different approach.

    COALLESCE returns the first not null value, it can be used with a variable number of arguments and it will return the value of the first not null argument. Values are evaluated from left to right.

    José Cruz
    • Marked as answer by Rjoseph2010 Thursday, February 11, 2010 1:48 AM
    Wednesday, February 10, 2010 10:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    COALLESCE returns the first not null value.

    How is it advantageous compared to ISNULL()?
    Chase Excellence - Success Will Follow!
    Thursday, February 11, 2010 5:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    COALLESCE returns the first not null value.

    How is it advantageous compared to ISNULL()?
    Chase Excellence - Success Will Follow!

    COALESCE is more advantageous than ISNULL for the following reasons:

    1) It takes multiple arguments
    2) COALESCE is an ANSI standard function, so it would be recognized by other SQL platforms... ISNULL is a proprietary Microsoft extension of T-SQL
    3) ISNULL has an annoying quirk to it:

    declare @c char(1)
    set @c=null
    select isnull(@c,'12876387682762187681768276873687687682')
    /* The above returns '1' */

    ISNULL returns the EXACT SAME DATATYPE as the first argument.  Since @c was defined as a CHAR(1), that's what ISNULL is going to return.  Thousands of people have gotten bit by that one.

    --Brad (My Blog)
    Thursday, February 11, 2010 5:39 AM