Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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.
0
pvsbandi
Asked:
pvsbandi
  • 6
  • 3
  • 2
  • +1
1 Solution
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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
 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now