SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Richard Spalding / Using Coalesce in a stored Procedure / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:29:11 GMT20RE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspx[quote][b]Edward Boyle (6/26/2009)[/b][hr]Agreed, but XML constructs are part of T-SQL and therefore, always available[/quote]If you are using SQL Server 2005 and above... FOR XML PATH is not available in SQL Server 2000.Fri, 26 Jun 2009 13:35:04 GMTj.a.cRE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxAgreed, but XML constructs are part of T-SQL and therefore, always availableFri, 26 Jun 2009 07:52:43 GMTEdward Boyle-478467RE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxAll 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.Fri, 26 Jun 2009 07:13:51 GMTrspaldingRE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxI'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.Fri, 26 Jun 2009 06:59:21 GMTNoel McKinneyRE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxI find the use of XMl to be a cleaner method to concatenate rows into a single value:select Fname+','From EmployeeFor XML Path(''), TypeFri, 26 Jun 2009 06:00:37 GMTEdward Boyle-478467RE: Using Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxPlease 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.Fri, 26 Jun 2009 00:32:44 GMTDavid McKinneyUsing Coalesce in a stored Procedurehttp://www.sqlservercentral.com/Forums/Topic734363-1565-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Stored+Procedure/67194/">Using Coalesce in a stored Procedure</A>[/B]Sat, 13 Jun 2009 10:20:04 GMTrspalding