Veena Shetty wrote : >-----Urspr�ngliche Nachricht----- >Von: Veena Shetty [mailto:[EMAIL PROTECTED] >Gesendet: Mittwoch, 10. November 2004 22:36 >An: [EMAIL PROTECTED] >Betreff: Assigning stored proc out parameter value to select >statement in another stored procedure > > >Hi, > >We have a stored procedure "SP_TRACKING_TAG"(see below) >that changes the value of the parameter 'Trackingtag' to new >tracking tag by looking for a comma in the >PARAM_TRACKINGTAG and splitting and constructing a >NEWTRACKINGTAG string. > >----------------------- > >CREATE DBPROC SP_TRACKING_TAG(IN >PARAM_TRACKINGTAG VARCHAR(255), OUT >NEWTRACKINGTAG VARCHAR(255)) >AS > >SET NEWTRACKINGTAG = 'Saf_' & PARAM_TRACKINGTAG >& '%'; > >VAR NEWTRACKINGTAG VARCHAR(255); REMAININGTT >VARCHAR(255); TEMPTT VARCHAR(255); COMMAPOS >INTEGER; >SET NEW_TRACKINGTAG=''; >SET COMMAPOS = INDEX(PARAM_TRACKINGTAG,','); >SET TEMPTT=PARAM_TRACKINGTAG; >SET REMAININGTT = SUBSTR >(PARAM_TRACKINGTAG,COMMAPOS+1,255); > >WHILE COMMAPOS > 0 >DO >BEGIN >SET NEW_TRACKINGTAG = NEW_TRACKINGTAG & SUBSTR >(TEMPTT,1,COMMAPOS-1) & '%'' or >dim_tracking_tag.tracking_tag like '''; >SET TEMPTT=REMAININGTT; >SET COMMAPOS = INDEX(TEMPTT,','); >SET REMAININGTT = SUBSTRING >(TEMPTT,COMMAPOS+1,255); >END; > >SET NEW_TRACKINGTAG = '''' & NEW_TRACKINGTAG & >TEMPTT & '%'''; > >------------------------- > > >I am making a call to "CALL SP_TEST('Saf,kris')" which calls >the stored proc SP_TRACKING_TAG and returns >NEWTRACKINGTAG=Saf% or dim_tracking_tag.tracking_tag >like 'kris%' . But it returns empty resultset for the select >statement in the stored proc SP_TEST(see below). > > >--------------------- > >CREATE DBPROC SP_TEST(IN PARAM_TRACKING_TAG >VARCHAR(255)) RETURNS CURSOR AS > >VAR NEWTRACKINGTAG VARCHAR(255); >CALL SP_TRACKING_TAG >(:PARAM_TRACKING_TAG,:NEWTRACKINGTAG); > > >$CURSOR = 'CURSOR1'; >DECLARE :$CURSOR CURSOR FOR >SELECT >DIM_TRACKING_TAG.TRACKING_TAG,DIM_TRACKING_TAG.T >RACKING_TAG_KEY FROM JDM.DIM_TRACKING_TAG >WHERE (DIM_TRACKING_TAG.TRACKING_TAG >LIKE :NEWTRACKINGTAG ) ; > > >---------------------------- > >Is there some other way to assign values of stored proc out >parameters to the select statement in another stored >procedure? Please advise. > >Thanks, >Veena > >-- >MaxDB Discussion Mailing List >For list archives: http://lists.mysql.com/maxdb >To unsubscribe: >http://lists.mysql.com/maxdb?>[EMAIL PROTECTED] >
Sorry, but I don't see any problems in your procedure code. I tested a call with some data and got the expected results. Maybe your table does not contain any qualified rows ? To clear the situation you should have a look into the vtrace. Look for the select statement called inside the procedure and the sql code returned by the execution of that statement. If 100 is returned, no data has been found. Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
