2

I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in. Unfortuantly where I work the previous people responsible for the database thought it would be a good idea to put comma delimited id lists inside of some columns. This function is the first step for me to rectify the problem without breaking the developers UI. Can anyone tell me how to fix this error? My code is below.

 CREATE FUNCTION fnSQLToDelimitedList ( @SQL NVARCHAR(MAX), @Delimiter NVARCHAR(3))
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @OUTPUT NVARCHAR(MAX) = ''
DECLARE @VALUE NVARCHAR(MAX)
DECLARE @TEMPVALUES TABLE (VALUE NVARCHAR(MAX) NOT NULL)


INSERT INTO @TEMPVALUES (VALUE)
EXECUTE sp_executeSQL @SQL

WHILE EXISTS (SELECT 1 FROM @TEMPVALUES)
BEGIN

SET @VALUE = (SELECT TOP 1 value
                FROM @TEMPVALUES)
SET @OUTPUT = @OUTPUT + ' ' +  @VALUE + @Delimiter

DELETE FROM @TEMPVALUES WHERE VALUE = @VALUE
END

SET @OUTPUT = SUBSTRING(@OUTPUT,0, LEN(@OUTPUT) )

    -- Return the result of the function
    RETURN @OUTPUT

END
GO
  • 5
    Functions on SQL Server are not the same as stored procedures, they have several limitations on the things that can be done. For example, you can't use dynamic SQL. – Lamak Jan 28 '13 at 15:25
  • 2
    I have no idea what you're passing in using @SQL but there are also much better string splitting techniques out there: for a few examples and performance comparisons, see sqlperformance.com/2012/07/t-sql-queries/split-strings and sqlperformance.com/2012/08/t-sql-queries/… – Aaron Bertrand Jan 28 '13 at 16:32
  • Also instead of passing in delimited strings in the first place have a look at table-valued parameters: sqlperformance.com/2012/08/t-sql-queries/… – Aaron Bertrand Jan 28 '13 at 16:33
  • Lamark - so I would want to use a stored procedure to accomplish this then it sounds like. – Lumpy Jan 28 '13 at 16:50
  • Aaron Bertrand - I'm looking to send in a SQL query as @SQL and return a delimited list of the results. – Lumpy Jan 28 '13 at 16:50
0

use this code to join rowset to delimited single value

sql,

use this code instead:

    DECLARE @IDs NVARCHAR(max)=''

    SELECT @IDs += ID +','
    FROM dbo.SomeTable

select @IDs

you can add where clause and design your query to return rowset with CTE and UNION ALL

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.