Answered by:
Coalesce()

Question
-
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 IdFROM 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
ThanksWednesday, February 10, 2010 9:59 PM
Answers
-
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 -
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 -
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
-
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 -
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 -
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 -
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 -
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