Using Coalesce in a stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Richard Spalding » Using Coalesce in a stored Procedure

Using Coalesce in a stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

rspalding

Posted Saturday, June 13, 2009 10:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 26, 2009 7:10 AM
Points: 4, Visits: 6

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

Post #734363

David McKinney

Posted Friday, June 26, 2009 12:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:43 AM
Points: 582, Visits: 1,601

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.

Post #742391

Edward Boyle-478467

Posted Friday, June 26, 2009 6:00 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:47 PM
Points: 51, Visits: 773

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

Post #742576

Noel McKinney

Posted Friday, June 26, 2009 6:59 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007, Visits: 767

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.

Post #742631

rspalding

Posted Friday, June 26, 2009 7:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 26, 2009 7:10 AM
Points: 4, Visits: 6

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.

Post #742648