Two functions that come in handy when building string lists from table values. The equivalent can also be done inline in the query, of course, for a slight boost in performance:
UPDATED 09/29/05: The inputs should check for empty string values and convert those to null:
--Adds an element (nvarchar) to the end of a list (nvarchar), after first inserting a delimiter (nvarchar)
--Updated 06/21/02 by Oskar Austegard
--Updated 09/02/05 by Oskar Austegard to use single COALESCE function
--Updated 09/29/05 by Oskar Austegard - Added empty string check
ALTER FUNCTION dbo.fnAddToList (@List nvarchar(4000), @New nvarchar(4000), @Del nvarchar(10))
RETURNS nvarchar(4000)
AS
BEGIN
--Treat ''s as NULLs
SELECT @List = NULLIF(@List, ''), @Del = NULLIF(@Del, ''), @New = NULLIF(@New, '')
--First try the concatened string, if null then just the list,
--if it too is null, just the new element
RETURN COALESCE(@List + @Del + @New, @List, @New)
END
--Adds a sql_variant element to the end of a sql_variant list, after first inserting a delimiter (nvarchar)
--If both element and list are nvarchars, using dbo.fnAddToList will be faster
--Created 07/13/05 by Oskar Austegard
--Updated 09/02/05 by Oskar Austegard - implemented COALESCE based approach to building list
--Updated 09/29/05 by Oskar Austegard - Added empty string check
ALTER FUNCTION dbo.fnAddVarToList (@VarList sql_variant, @VarNew sql_variant, @Del nvarchar(10))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @List nvarchar(4000), @New nvarchar(4000)
SELECT @List = NULLIF(CONVERT(nvarchar(4000), @VarList), ''),
@New = NULLIF(CONVERT(nvarchar(4000), @VarNew), '')
--First try the concatened string, if null then just the list,
--if it too is null, just the new element
RETURN COALESCE(@List + @Del + @New, @List, @New)
END
Labels: sql
