SP resultset to store in two object variables

Question

Post reply

SP resultset to store in two object variables

rhd110

Ten Centuries

Points: 1062
More actions
November 15, 2010 at 5:33 am

#231553

Team,
I have a store procedure which on execution yields 2 resultset.
I want to store the 2 result set in two different object variables. Is this possible.
The result sets are as follows ---------------
ResultSet 1 :
ProductID
ProductName
ResultSet 2 : (Product mapped to its Vendor)
VendorID
VendorName
VendorEmailAddress
--------------------------------------------
What is the right way to do this ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

I don't think it is possible to do it directly.

You can however:

store the results sets in two table variables. Merge these two variables with a union all and add some sort of 'sorting column' (e.g. resultset1 and resultset2).

Store the unioned result set in an object variable.

When you read the data from this variable, you can easily separate the two result sets with a conditional split.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rhd110 Ten Centuries Points: 1062 More actions · Answer 2

Team,

First of all, thanks for replying to my post.

But sorry to say, I didnot get ur answer.

U want me to merge these resultset using an union all, but in my case the two resultsets are having no common column.

So for me it is not possible to MERGE the resultsets.

Even if I merge the resultset on what condition would I split the resultset again using the conditional split operator.

Thanks.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Sure you can use a union all.

ProductID and VendorID are probably both integers.

ProductName and VendorName are probably both strings.

You can place NULL in the third column of the first resultset and union that with VendorEmailAddress.

Add a 4th column (int) with the following values: 1 for resultset1 and 2 for resultset2. Name this column 'SplitCondition'.

In the conditional split, use the following expression:

SplitCondition == 1

Then you'll have two streams, one for resultset1, and one for resultset2.

See team, that wasn't so hard.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP