Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Coalesce in a stored Procedure Expand / Collapse
Author
Message
Posted Saturday, June 13, 2009 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, June 26, 2009 12:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:02 AM
Points: 648, Visits: 1,874
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
Posted Friday, June 26, 2009 6:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:12 AM
Points: 60, Visits: 903
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
Posted Friday, June 26, 2009 6:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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
Posted Friday, June 26, 2009 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, June 26, 2009 7:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:12 AM
Points: 60, Visits: 903
Agreed, but XML constructs are part of T-SQL and therefore, always available
Post #742690
Posted Friday, June 26, 2009 1:35 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:24 AM
Points: 583, Visits: 3,557
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.
Post #742946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse