COALESCE as Concatenation Operator
I always forget the details of how to do this and have to research it again. So here for both your and my benefit is how you can use the SQL Server COALESCE function to retrieve text from multiple records and concatenate it together.
The need I had today was to pass a string like '7,2,8' to a stored procedure and get back a string like this: ‘Alaska Natives, Art Education, Art History’. You can probably guess that the numbers are primary keys into a lookup table with areas of interest for grant funding. The numbers come from reading the value of a collection of checkboxes on an ASP Web form (yes, ASP classic, sigh).
I could have done three (or however many) calls to the database, but that is bad practice. So I used the COALESCE function like this (this is my test code before I turned it into a stored procedure:
DECLARE
@criteria NVARCHAR(1000)
DECLARE @aoilist NVARCHAR(100)
SET @aoilist = '7,2,8' -- test data; string can be any number of indices
SET
@aoilist = ',' + @aoilist + ',' -- put delimiters at start and end
SELECT
@criteria = COALESCE(@criteria + ', ' + aoi.ncv_DecAreaOfIntr_Desc, aoi.ncv_DecAreaOfIntr_Desc)
FROM tblFunder_DecAreasOfInterest_dfai aoi
WHERE CHARINDEX(',' + CAST(int_Recordid AS nvarchar) + ',', @aoilist) > 0
PRINT
@criteria
According to BOL, the COALESCE function “Returns the first nonnull expression among its arguments.” That description doesn’t begin to hint at this trick, though!
Most commonly the WHERE clause uses an EXISTS clause with a subquery to return data from another table. But in this case another table wasn’t involved, and I just used the @aoilist value to provide the test.
I originally learned of this trick a few years back in the ASP.NET forums, on which I’m a moderator. It has simplified my coding a lot since then!