i need to write a stored proc in which user should be able to pass multiple values for one parameter but for each value the stored proc should return a separate data set. for eg suppose the query in the proc sp_emp is
select * from emp where emp in @emp
now when user executes the proc like exec sp_emp 1,2,3,4,5,6
the stored proc shoulld return 6 different data sets for each of the emp value.
This solution contains a Split-function which is "stolen" from Jeff Modens article at SQL Server Central:
Here's the function code:
And here's how to use it in a stored procedure to get a result set for each input-parameter in a comma-separated string.
answered Oct 31 '11 at 09:46 AM
The first question that springs to mind is 'Why?'.
You can do this, but I wouldn't recommend doing it this way...
You would have a wrapper proc that serves 2 purposes: 1- to split the input parameter into individual
Because each result set (of one row) is going to be identical in terms of the columns, why not have the data returned as one result set, and have the calling application either handled it that way, or, if you must, split it into multiple result sets in it's domain.
answered Oct 31 '11 at 09:07 AM
Kev Riley ♦♦