CODEWITHSUNDEEP
sqlsql-server
Jack Thor
Jack Thor

Reputation: 1594

What is the result set returned as?

So I have this stored procedure that queries a table and return a result set, but I am using that result set as a param for a store procedure that I execute inside my store procedure. 

SELECT employeeID FROM employeeList where LastName = 'Zhou'
..... Some stuff .....
exec my_store_proc @employeeID = 'a comma separated string of employee ID'

Where my_store_proc has a param @employeeID AS VARCHAR(8000) = '',. I know I can use a cursor to traverse the result set and concatenate them into a comma separated string. But I was wondering what is the result set returned from the select statement alone, does it have a data type? I was hoping the answer would be a comma separated string already so that I can just set employeeID = employeeID. But my gut feeling says no.

Upvotes: 0

Views: 86

Answers (1)

suff trek
suff trek

Reputation: 39777

As it is your SELECT produces a result-set, which is techically a table. But you can collect result of this SELECT into a comma separated Varchar:

DECLARE @sResult VARCHAR(8000)
SET @sResult = ''

SELECT @sResult = @sResult + ',' + CAST(employeeID as varchar(10)) 
FROM employeeList where LastName = 'Zhou'

SET @sResult = STUFF(@sResult,1,1,'')

Basically you declare a varchar variable then use a form of SELECT that collects values into a single string (assuming employeeID is an INT hence CAST)

Last line removes leading comma.

After that you can make a call

exec my_store_proc @employeeID = @sResult

Upvotes: 3

Related Questions