x

Different data sets in one query

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
more ▼

asked Oct 31 '11 at 08:56 AM in Default

NAmola gravatar image

NAmola
134 12 13 15

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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
more ▼

answered Oct 31 '11 at 09:46 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

Just adding one thing: Be careful with what you pass to the stored procedure now that it uses dynamic SQL.
Oct 31 '11 at 10:10 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 31 '11 at 09:07 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

thanks for going through it Kev. for your question why, i just want to know the code to write a stored proc which can give multiple data sets on execution where the number of data sets depends on the number of values of the parameter. the actual requirement is much more complex then just a simple select statement. i guess it will require looping but i am not exactly sure how and i am in too much hurry to ponder over it for too long :|
Oct 31 '11 at 09:16 AM NAmola
I would seriously reconsider your design then. No kidding.
Oct 31 '11 at 09:19 AM Kev Riley ♦♦

its hard to explain, but let me try. we need number of male and female employees for each ethnicity in each job category for each location in an organization. and we need to put it in a crystal report. now client wants that wen he selects multiple locations, he should have both options 1) of getting a consolidated report for all locations and 2) a different report for each location. we are unable to handle this at report level so we decided to handle this at query level. if we can get a separate data set for each location value, we can get a different report for it. getting the consolidated data set is not a problem as that is how a stored proc normally works. bt getting a different data set is a problem :/

so now if i was able to explain u, can u plz help? :)
Oct 31 '11 at 09:31 AM NAmola

I might not be understanding this correctly, but if there is a requirement for a different report per location, then I would have a different proc per location. However you have said that you want the same proc to be called but with different parameters - which brings it back round to being the same 'shape' of data for each location.

If you can handle having a report definition for each data set in the proc output, why not simply have a report definition for 'filtered' data. I'm not a Crystal Reports expert, but I'm sure you can filter the datasource.

How would you determine if the call to the proc returns 1, 2, 5, 10, 100 record sets?
Oct 31 '11 at 09:48 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x72
x8

asked: Oct 31 '11 at 08:56 AM

Seen: 787 times

Last Updated: Oct 31 '11 at 09:49 AM