Conditionally call the stored procedure

Hi,
 
   I have a report whose columns are populated based on a stored procedure call.
    The call is like this.
      CALL SP_RPT_88R(ID,S_ID,TYPE). S_ID is always 0 for this procedure.
      
      Now, i have another procedure SP_RPT_89R(ID,S_ID,TYPE). for this, S_ID is always greater than zero.
       These two stored procedures have very identical result sets.
      
        I need to conditionally call the procedures in the Crystal reports, based on the S_ID.
        How can i do that? Please help.
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
You  can create another procedure which will call
SP_RPT_88R(ID,S_ID,TYPE) or SP_RPT_89R(ID,S_ID,TYPE) depends of the ID

something like this
CREATE PROCEDURE  SP_RPT_8XR
@ID INT,
@S_ID XXXXX,
@TYPE XXXXX
AS
BEGIN

SET NOCOUNT ON      
if @ID=0
  exec SP_RPT_88R(@ID,@S_ID,@TYPE)
else
  exec SP_RPT_89R(@ID,@S_ID,@TYPE)
 
END


Then you will deal with one procedure
0
pvsbandiAuthor Commented:
I actually did that. But when i call the parent procedure, it is not returning the result set.
 Instead, it is just returning the result status as 0, meaning success..
0
vastoCommented:
Can you try with the script that I sent ? just change the type of the parameters (XXXX).
Try it in Management studio just to see if the result will be OK
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

pvsbandiAuthor Commented:
I'm on Db2 8.2..My code is very much similar to yours
CREATE PROCEDURE CHESSIE.SP_RE396R_OTHER (IN V_SCREENING_ID INTEGER,V_SNAPSHOT_ID INTEGER,IN V_TYPE VARCHAR(5))
SPECIFIC SP_RE396R_OTHER

   IF V_SNAPSHOT_ID = 0 THEN
   CALL CHESSIE.SP_RE396R_BS_OTHER(V_SCREENING_ID,0,V_TYPE);
  ELSEIF
      V_SNAPSHOT_ID > 0 THEN
   CALL CHESSIE.SP_RE396R_SS_OTHER(V_SCREENING_ID,V_SNAPSHOT_ID,V_TYPE);
  END IF;

END P1
            

Open in new window

0
mlmccCommented:
Where did you add that code in Crystal?

You may have to build that in the database.

mlmcc
0
pvsbandiAuthor Commented:
I have this in the database only.. I'm calling the "SP_RE396R_OTHER" stored procedure in the crystal.
  but it is not getting anything back..
0
vastoCommented:
I am sorry, I am not good with DB2.  I was talking about SQLServer
What is "END P1" doing ? Is it OK to remove this line ?
0
pvsbandiAuthor Commented:
End P1 is DB2 specific..valid here..

   Can anyone add DB2 to the zones please?
0
momi_sabagCommented:
basically your problem is returning the result set from the nested procedure
take a look here
http://bytes.com/topic/db2/answers/184569-passing-resultset-nested-stored-procedure
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
So, shall i be using "Return to Client" instead of "Return to Caller" ?
0
momi_sabagCommented:
according to documentation - yes
0
pvsbandiAuthor Commented:
Thank you so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.