Re: dbproc + dynamic query result

Hi,

Oh, I see what you mean now. Sorry about that. No, I don't actually know 
if what you are trying to do is possible. I would, however, be very 
interested to find out if it is. I know that if this were embedded SQL 
you could do something like that with a prepare/execute series of 
statements. However, PREPARE doesn't seem to be available in stored 
procedures.

Anyone?

Best,
JLS

edlef@xxxxxxxxxxx wrote:

> Hi,
>
> Thanks, but it is important that the select and the cursor should be 
> made within a string and than executed. I write for both question a 
> more specific example why is it so.
>
> Here an example for the result of a dynamic query:
>
> CREATE DBPROC INSERTBEREND_PASZTOR (
>         IN TABLEIDX             VARCHAR(4),
>         IN FOLYAMAT             VARCHAR(30),
>         IN IDO                  TIMESTAMP,
>         IN D501                 VARCHAR(10)
>         )
>         AS
>         VAR
>                 HEADID          INTEGER;
>                 FOLYAMATID      INTEGER;
>                 IQ                      VARCHAR(500);
>                 INS                     VARCHAR(500);
>                 ISSTORED        INTEGER;
>                 DID                     INTEGER;
>         TRY
> 		SET IQ = 'SELECT COUNT(*) FROM 
> DNUSER.DATAHEADER' || TABLEIDX || ' WHERE BERID = 1 AND IDO = ''' 
> || IDO || '''';
>                 EXECUTE IQ;
>                 IF( ISSTORED > 0 ) THEN RETURN;
>                 FETCH MARVAN INTO :ISSTORED
>                 SELECT SEQ_HEADID.NEXTVAL FROM DBA.DUAL;
>                 FETCH INTO :HEADID;
>                 CALL FOLYAMAT_UJ( 1, :FOLYAMAT, :FOLYAMATID );
>
> 		SET IQ = 'INSERT INTO DNUSER.DATAHEADER' || 
> TABLEIDX ||
> 			' ( HEADERID, BERID, IDO, FOLYAMATID ) ' 
> ||
>                         ' VALUES( ' || HEADID || ', 1, ''' ||
>                         IDO || ''', ' || FOLYAMATID || ' )';
>
>                 EXECUTE IQ;
> 		SET INS = 'INSERT INTO DNUSER.DATA' || TABLEIDX 
> || ' ( DATAID, HEADERID, PARAMID, ERTEK )  VALUES ';
>
> 		SELECT SEQ_DATAID.NEXTVAL INTO :DID FROM 
> DBA.DUAL;
> 		SET IQ = INS || '( ' || DID || ', ' || HEADID ||', 1, '''|| 
> D501 || ''')';
>                 EXECUTE IQ;
> 		INSERT INTO DNUSER.TEMPIDOSZAKIJELENTES	( 
> BERID, FOLYAMATID, PARAMID, DATUM, ERTEK )
>                 VALUES( 1, :FOLYAMATID, 1, :IDO, NUM( :D501 ) );
>         CATCH
>                 IF( $RC <> 0 )
>                 THEN STOP ( $RC, $ERRMSG );
>
>
> The example for the returned cursor should be created whitin a string:
>
> CREATE DBPROC MINTAINDITAS_GET_LIST(
>         IN F_STATUS INTEGER,
>         IN F_MINTAKOD VARCHAR,
>    IN F_MVHELYID INTEGER,
>    IN F_MINTAVETELIIDO1 VARCHAR,
>         IN F_MINTAVETELIIDO2 VARCHAR,
>    IN F_VIZTIPUS INTEGER,
>         IN F_TIPUS INTEGER)
>         RETURNS CURSOR AS
>    VAR FILTER VARCHAR;
>    $CURSOR = 'MLIST_CUR';
>         FILTER = ' status = ' || F_STATUS;
>         IF (  F_MINTAKOD!='' ) THEN
>    	FILTER = FILTER || ' AND mintakod like ''%' || F_MINTAKOD 
> || ''' ';
>         IF ( F_MVHELYID != 0 ) THEN
>             FILTER = FILTER || ' AND mvhelyid = ' || F_MVHELYID;
>         IF (  F_MINTAVETELIIDO1 != '' ) THEN
> 		FILTER = FILTER || ' AND mintaveteliido >= ''%' || 
> F_MINTAVETELIIDO1 || ''' ';
>         IF (  F_MINTAVETELIIDO2 != '' ) THEN
>    	FILTER = FILTER || ' AND mintaveteliido <= ''%' || 
> F_MINTAVETELIIDO2 || ''' ';
>         IF ( F_VIZTIPUS != 0 ) THEN
>         FILTER = FILTER || ' AND viztipus = ' || F_VIZTIPUS ;
>         IF ( F_TIPUS = 2 ) THEN
>         FILTER = FILTER || ' AND parhuzamosminta = 1' ;
>         IF ( F_TIPUS = 3 ) THEN
>         FILTER = FILTER || ' AND ellenminta = 1';
>         DECLARE :$CURSOR CURSOR FOR
>    	SELECT * FROM KFCSUSER.view_mintainditas_lista 
> WHERE :FILTER;
>
>
> Thanks,
> Norbert
>  

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx