hi 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. Thanks
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 `emp`s, 2- to call another proc for each `emp`, thereby returning multiple result sets. I'm also assuming here that the list of `emp`s is passed in as a list and not with 100's of optional parameters :/ But really....... 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.
This solution contains a Split-function which is "stolen" from Jeff Modens article at SQL Server Central: [
http://www.sqlservercentral.com/articles/Tally+Table/72993/] Here's the function code: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT t.N+1 FROM cteTally t WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1), Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)) FROM cteStart s GO 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. CREATE PROC myProc (@emp varchar(500)) AS DECLARE @s nvarchar(500) SET @s='' SELECT @s= @s + 'SELECT * FROM as Employee where EmpID = ' + item + '; ' from dbo.DelimitedSplit8K(@emp,',') EXEC sp_executesql @s :