Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

closed as too localized by Mark Storey-Smith, dezso, Jon Seigel, Max Vernon, RolandoMySQLDBA Apr 29 '13 at 17:12

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

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

1 Answer 1

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

share|improve this answer

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