?
Solved

Return comma seperated list of sql result

Posted on 2007-08-02
6
Medium Priority
?
1,059 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:UniqueData
  • 3
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 19621647
declare @result varchar(1000)
select @result=isnull(@result + ',' + FirstName, FirstName) from Users where DeptID=3
0
 
LVL 7

Author Comment

by:UniqueData
ID: 19625285
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19626134
>> 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 19626317
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
 
LVL 7

Author Comment

by:UniqueData
ID: 19627104
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19627739
:-).
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question