mo.notono.us

Thursday, September 29, 2005

SQL: fnAddToList() and fnAddVarToList()

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:

0 Comments:

Post a Comment

<< Home