Invalid use of side-effecting operator 'INSERT EXEC' within a function 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
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