question

NAmola avatar image
NAmola asked

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
stored-proceduresparametersdataset
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
I would seriously reconsider your design then. No kidding.
2 Likes 2 ·
NAmola avatar image NAmola commented ·
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 :|
0 Likes 0 ·
NAmola avatar image NAmola commented ·
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? :)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
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?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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/][1] 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 [1]: http://www.sqlservercentral.com/articles/Tally+Table/72993/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Just adding one thing: Be careful with what you pass to the stored procedure now that it uses dynamic SQL.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.