Return comma seperated list of sql result

I need a procedure (or function I guess) that will return as a string the values of a passed sql statement as comma seperated string.

In other words, I pass the string 'Select FirstName From Users Where DeptID = 3' and the returned string would be 'Joe, Fred, Wilma'

Is this possible?
LVL 7
UniqueDataAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The procedure below is designed for a *single column* in the query.  You also cannot use TOP or DISTINCT.  Things like TOP and DISTINCT could be allowed most easily by adding a character that marked the beginning and end of the concat column [such as "SELECT ~FirstName~ FROM ..." or "SELECT TOP 10 DISTINCT ~UserName~ FROM ...."], but I didn't want to cloudy the query with that.


CREATE PROCEDURE dbo.ConcatResults
    @sql NVARCHAR(4000)
AS
DECLARE @result VARCHAR(8000) --<<-- use VARCHAR(MAX) in SQL 2K5
SET @sql = STUFF(@sql, CHARINDEX(N'SELECT ', @sql) + 7, 0,
    N'@result = ISNULL(@result + '', '', '''') + ')
SET @sql = STUFF(@sql, CHARINDEX(N'FROM ', @sql) - 1, 0,
    N' + ''''')
--PRINT @sql
EXEC sp_executesql @sql, N'@result VARCHAR(8000) OUTPUT', @result OUTPUT
SELECT @result
GO


EXEC dbo.ConcatResults 'Select FirstName From Users Where DeptID = 3'
0
 
dqmqCommented:
declare @result varchar(1000)
select @result=isnull(@result + ',' + FirstName, FirstName) from Users where DeptID=3
0
 
UniqueDataAuthor Commented:
That works great when the sql statement is fixed, but I need to be able to pass a sql statement to make the function more flexible.  This way I can use it for showing names from the Users table, Student List from the Student table, etc.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Scott PletcherSenior DBACommented:
>> Is this possible? <<

That depends.  If you are using SQL 2005, yes.  If you are using SQL 2000, yes if the total length of the output is > 8000 bytes [it can be done in SQL 2K for over 8K bytes, but that requires using TEXT columns, which are such a pain that I won't attempt to write it].

It will have to be a stored proc, not a function, since you will need dynamic SQL.  Will post a possible solution ASAP.


0
 
UniqueDataAuthor Commented:
that worked PERFECTLY.  I do have sql 2000 so lets hope that I don't need more than 8000 characters (I wouldn't think so).

I see where you are looking for the field and table names in @sql, but I just wish I knew what the heck the rest of the code is doing!!!
0
 
Scott PletcherSenior DBACommented:
:-).
First, one quick correction, which technically doesn't hurt anything but is not needed.  This line can be removed:
SET @sql = STUFF(@sql, CHARINDEX(N'FROM ', @sql) - 1, 0, N' + ''''')

OK, now to what it's doing.  It's turning this:
Select FirstName From Users Where DeptID = 3
into this:
Select @result = ISNULL(@result + ', ', '') + FirstName From Users Where DeptID = 3

That will get the concatenated values into a variable [to prove it, you can declare @result, run the above code in qa, and add a PRINT @result to see the value of the variable).  

But that variable still has to be gotten from the dynamic SQL back to the procedure code.  That's where sp_executesql comes in.  It ran return a value, but only if it's specified as an OUTPUT parameter on both the variable declaration(s) [parameter 2 to sp_exec] and the return variable(s) [params 3 thru however many vars you have].  Check the code in the stored proc and you'll see that is what was done.

Here's another example:
DECLARE @date SMALLDATETIME
EXEC sp_executesql N'SELECT @date = GETDATE()', N'@date SMALLDATETIME OUTPUT', @date OUTPUT
PRINT @date

Contrast that with this (NOTE: this code causes an error):
DECLARE @date SMALLDATETIME
EXEC('SELECT @date = GETDATE()')

In summary, the way SQL Server functions means:
1) you have to use dynamic SQL for the main query -- since it is completely supplied by the user at run time.  
2) You also have to modify the query to do concatenation of values, since that is the type of result set you want.  
3) That requires using a variable.  In order to get the back the value of a variable from dynamic SQL, you must use sp_executesql.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.