asked on

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.

vasto

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

pvsbandi

ASKER

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

vasto

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

pvsbandi

ASKER

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

Mike McCracken

Where did you add that code in Crystal?

You may have to build that in the database.

mlmcc

pvsbandi

ASKER

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

vasto

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 ?

pvsbandi

ASKER

End P1 is DB2 specific..valid here..

Can anyone add DB2 to the zones please?

ASKER CERTIFIED SOLUTION