Using Coalesce in a stored Procedure

Question

Using Coalesce in a stored Procedure

rspalding

SSC Veteran

Points: 218
More actions
June 13, 2009 at 10:20 am

#205956

Comments posted to this topic are about the item Using Coalesce in a stored Procedure

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David McKinney SSChampion Points: 10472 More actions · Answer 1

Please correct me if I've misunderstood but....

Coalesce returns the first non-null value among its parameters (from first to last). Given that your second parameter is

not null (a space), the third parameter is superfluous.

COALESCE(@COUNTYCRITERIA,' ',',')

If you initialise your variable @COUNTYCRITERIA to an empty string first, then you don't have to coalesce anything.

i.e.

DECLARE @COUNTYCRITERIA varchar(1000)

SET @COUNTYCRITERIA=''

SELECT....

Regards,

David McKinney.

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 2

I find the use of XMl to be a cleaner method to concatenate rows into a single value:

select Fname+','

From Employee

For XML Path(''), Type

Noel McKinney Hall of Fame Points: 3452 More actions · Answer 3

I'm with David, the use of two rightmost strings in the COALESCE function doesn't make sense. You'll never get to the last (third) parameter. With COALESCE only the last (rightmost) parameter should ever be a fixed value and each of those to the left of it should be variables or columns that could be null.

rspalding SSC Veteran Points: 218 More actions · Answer 4

All great comments!! I agree that XMI might give you cleaner results. this example was written to show how to concatinate rows when XMI isnt an option in stored procedures or SQL only is required.

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 5

Agreed, but XML constructs are part of T-SQL and therefore, always available

j.a.c SSCrazy Points: 2283 More actions · Answer 6

Edward Boyle (6/26/2009)
Agreed, but XML constructs are part of T-SQL and therefore, always available

If you are using SQL Server 2005 and above... FOR XML PATH is not available in SQL Server 2000.