April 11, 2012 at 10:15 am
Is it possible to return more than one data set from a stored proc?
I have a situation where I need to generate on-the-fly roughly 20 reports which the finance group wants created from the same data extract. (As in one pull of data)
Additionally, this will need to be able to be run by multiple people at the same time.
I would be interested in any suggestions on methods to go about this.
April 11, 2012 at 12:59 pm
Absolutely.
The following TSQL will produce two output sets.
If you put this TSQL into a stored procedure the application software you use should be able to select which output set you want from that procedure.
-- Create and load a table for this demo
DECLARE @myTbl TABLE (Person varchar(20), Age int)
INSERT INTO @myTbl VALUES ('Revere, Paul', 83)
INSERT INTO @myTbl VALUES ('Adams, Samuel', 81)
INSERT INTO @myTbl VALUES ('Hancock, John', 56)
INSERT INTO @myTbl VALUES ('Lincoln, Abraham', 56)
INSERT INTO @myTbl VALUES ('Washington, George', 68)
-- create first output set
SELECT
people.Person
FROM @myTbl AS people
ORDER BY people.Person
-- create second output set
SELECT
people.Person,
people.Age
FROM @myTbl AS people
ORDER BY people.age DESC, people.Person
- Chuck Hoffman
Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------
April 12, 2012 at 7:10 am
THe harder part of returning multiple result sets is for your application to be aware of the multiple result sets.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 12, 2012 at 8:03 am
This is in preparation for an upgrade of the app, which will be replaced with reporting services. I am hoping that would not be too difficult, but I don't know yet.
April 12, 2012 at 8:06 am
I agree with Jack.
It happens that most of my applications are written in ColdFusion. Each language would be different but you can get the idea from the following ColdFusion sample.
The code for executing the proc would look like this:
<cfstoredproc procedure="proc-name" datasource="dsn">
<cfprocparam ... >
<cfprocparam ... >
<cfprocresult name="first-set-name" resultset="1">
<cfprocresult name="second-set-name" resultset="2">
</cfstoredproc>
The code for retrieving the second set would look like this:
<cfif IsDefined("second-set-name") AND (second-set-name.RECORDCOUNT GT 0)>
... process the second resultset ...
</cfif>
Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply