Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Using Coalesce in a stored Procedure

By Richard Spalding, 2009/06/26

If you have a column of data and you want to concatinate it into a single variable for use in a stored procedure then the above code offers one way to do it.

This can of course be modified to to handle and combine multiple columns of data into a single (comma delimited ) string.

-- @cd_county = '1','2','3'
--lets say that you have a string of data like the one above.
--the code below illustrates one possible way to take the the multiple 
--rows that would be returned and place them in a variable.
-- this is especially useful for reporting purposes where the developer 
--is required to keep everything within the stored procedure 
--

declare @str as varchar (max)
declare @COUNTYCRITERIA varchar(max)
set @str = 'INSERT INTO #TEMP_COUNTYNAME (NM_COUNTY)(select DS_DESC from CountyCodes where TYPE=''COUNTY'' and VALUE in('+ @cd_county+ '))' 
exec(@str)

SELECT @COUNTYCRITERIA = COALESCE(@COUNTYCRITERIA,' ',',') + NM_COUNTY + ', ' FROM #TEMP_COUNTYNAME
PRINT @COUNTYCRITERIA
drop table #TEMP_COUNTYNAME

Total article views: 1851 | Views in the last 30 days: 1