| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Maximum No. of Result Sets for a stored procedure definition |
« View previous topic :: View next topic » |
| Author |
Message
|
| pradeep.behera |
 Posted: Mon Jul 11, 2016 3:27 am Post subject: Maximum No. of Result Sets for a stored procedure definition |
 |
|
Newbie
Joined: 05 May 2009
Posts: 3
|
A SQL Server Stored procedure is returning dynamic number of result sets in each call in esql. There is a need to process varying number of result sets returned by the stored procedure in each call.
For E.g sometimes result sets returned will be 3, sometimes it will be 5.
The best way, I can think of to define max possible resultsets in the definition as shown below. If it comes out be less than 5 or whatever declared, will ignore the rest ones.
| Code: |
| CREATE PROCEDURE SP_Messages(IN MessageType CHARACTER) LANGUAGE DATABASE DYNAMIC RESULT SETS 5 EXTERNAL NAME "xyz.SP_Messages"; |
Its running on IIB 9.0.0.3 on AIX 7.1.
The problem here I do not have max possible number of result sets the stored procedure can return.
1. Is there any other way to handle varying number of result sets returned by a stored procedure call ?
2. What is the maximum number of dynamic result sets allowed in the stored procedure definition in IIB? Is there any limit?
I tried to get the max possible resultsets from the info centre and google but no luck. Could you please help.
_________________
Regards,
Pradeep Kumar Behera
IBM Certified Solution Designer
IBM Certified System Administrator |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Jul 11, 2016 4:13 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You could
- Pass the max number of results you expect to the SP via a parameter.
- Modify the SP to do something like
| Code: |
select top N from ....
|
where N is the number of results expected and passed to the SP
However, what happens when there are more results to be returned than you expect?
What results do you want to throw away in the SP?
You need to be careful here so that you get the right set of results.
Personally, I'd get the whole lot returned to Broker and work on it there.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jul 11, 2016 4:28 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Ambiguity:
Is the OP talking about the max number of rows in a result set, or
talking about the max number of dynamically declared result sets...
I believe there is a problem here as you would need to declare all the returned result sets in the procedure signature... and probably have to return a null (if this is supported) for the result sets not used.
Oracle may allow you to declare a procedure with a varying number of result sets. I don't believe that IIB does. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pradeep.behera |
| Posted: Mon Jul 11, 2016 5:43 am Post subject: |
|
|
Newbie
Joined: 05 May 2009
Posts: 3
|
Thanks fjb_saper. You are talking about the the maximum no of rows. What I meant was the number of RESULTSETS returned by the stored procedure.
Thanks smdavies99. its about the max number of dynamically declared result sets. Yes, I have to declare number of returned result sets in the signature as well as during call like below:
| Code: |
CALL SP_Messages(MessageType,
Environment.V1.SPMsg[],
Environment.V2.SPMsg[],
Environment.V3.SPMsg[],
Environment.V4.SPMsg[],
Environment.V5.SPMsg[]) ; |
Can I get to know the max possible dynamic result sets could be defined in the definition. I tested with 100 dynamic result sets, its working fine. Need to know exact number it can support. May be I have to do more trial & error to find out exact number of dynamic parameters allowed.
_________________
Regards,
Pradeep Kumar Behera
IBM Certified Solution Designer
IBM Certified System Administrator |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jul 11, 2016 7:38 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Open a PMR. IBM may well tell you it depends on your resources...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
