Different data sets in one query

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

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 emps, 2- to call another proc for each emp, thereby returning multiple result sets. I'm also assuming here that the list of emps 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.