Execute a procedure stored in Advantage Data Architect

Question

Execute a procedure stored in Advantage Data Architect

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I am creating an application with the Advantage database server. I want to insert a data using the stored procedure

I tried an input parameter, this is the stored procedure:

 CREATE PROCEDURE SP_FolioComp
 ( 
 @NumFolio CHAR ( 11 )
 ) 
 BEGIN 

 SELECT COMPRAS.NO_REFEREN, NO_FACT_PR, NUMERO_A, F_FACTURA, ALMACEN, 
 TIPO_MON, CANTIDAD, CLAVE_ART, DESCUENTO, DESCUENTO2, DESCUENTO3, 
 DESCUENTO4, COSTO, IEPS, IMPUESTO, COSTO + IMPUESTO + ISNULL (IEPS, 0) AS 
 TOTAL
 FROM COMPRAS JOIN PARTCOMP ON COMPRAS.NO_REFEREN = PARTCOMP.NO_REFEREN
 WHERE COMPRAS.NO_REFEREN = @NumFolio;
 END;

When executing the stored procedure:

 EXECUTE PROCEDURE SP_FolioComp
 (
 'A0000000012'
 );

I get this error:

 poQuery: Error 7200:  AQE Error:  State = HY000;   NativeError = 5154;  [iAnywhere Solutions][Advantage SQL][ASA] Error 5154:  Execution of the stored procedure failed.  Error 7200:  AQE Error:  State = S0000;   NativeError = 2121;  [iAnywhere Solutions][Advantage SQL Engine]Column not found: @NumFolio -- Location of error in the SQL statement is: 315 (line: 4 column: 28)

sql

asked by SoyKrut 20.12.2017 в 01:08

source

2 answers

1

As I see you need to receive the parameter put it in a variable, put it in your query and declare each of the fields that your query will return with the word OUTPUT and declare a CURSOR.

    CREATE PROCEDURE SP_FolioComp
    (
       NumFolio CHAR ( 11 ),
       NOREFERENV ["TipoDato"] ( ? ) OUTPUT,
       NOFACTPRV ["TipoDato"] ( ? ) OUTPUT,
       NUMEROAV ["TipoDato"] ( ? ) OUTPUT,
       FFACTURAV ["TipoDato"] ( ? ) OUTPUT,
       ALMACENV ["TipoDato"] ( ? ) OUTPUT,
       TIPOMONV ["TipoDato"] ( ? ) OUTPUT,
       COSTOV ["TipoDato"] ( ? ) OUTPUT,
       IEPSV ["TipoDato"] ( ? ) OUTPUT,
       IMPUESTOV ["TipoDato"] ( ? ) OUTPUT,
       TOTALV ["TipoDato"] ( ? ) OUTPUT
    ) 

    BEGIN 
    DECLARE Fol CHAR (11);
    DECLARE cursor1 CURSOR;
    Fol = (SELECT NumFolio FROM __input);

    OPEN cursor1 AS

    SELECT NO_REFEREN AS NOREFERENV, 
           NO_FACT_PR AS NOFACTPRV,
           NUMERO_A AS NUMEROAV,
           F_FACTURA AS FFACTURAV,
           F_EMISION AS FEMISIONV, 
           ALMACEN AS ALMACENV,
           TIPO_MON AS TIPOMONV,
           COSTO AS COSTOV,
           IEPS AS IEPSV,
           IMPUESTO AS  IMPUESTOV,
           COSTO + IMPUESTO + ISNULL (IEPS, 0) AS TOTALV
           FROM COMPRAS 
           WHERE NO_REFEREN LIKE Fol;

    WHILE FETCH cursor1 DO
    INSERT INTO __output VALUES (
        cursor1.NOREFERENV,
        cursor1.NOFACTPRV,
        cursor1.NUMEROAV,
        cursor1.FFACTURAV,
        cursor1.FEMISIONV,
        cursor1.ALMACENV,
        cursor1.TIPOMONV,
        cursor1.COSTOV,
        cursor1.IEPSV,
        cursor1.IMPUESTOV,
        cursor1.TOTALV
        );
    END WHILE;
    CLOSE cursor1;

    END;

Try to put the same alias name to the output parameters.

answered by 09.01.2018 в 17:52

Application Title seen in the TOOLBAR How to refactor excess of conditions if?

score 2 · Accepted Answer

From what I can understand from the documentation for this database , it seems that the input parameters of a procedure are accessible through a virtual table called __input . And each parameter would be a column of this virtual table.

If this is true, then you should be able to add a join to this virtual table in order to access the parameter. Try something like this:

CREATE PROCEDURE SP_FolioComp(NumFolio CHAR(11)) 
BEGIN 
    SELECT COMPRAS.NO_REFEREN,
           NO_FACT_PR,
           NUMERO_A,
           F_FACTURA,
           ALMACEN, 
           TIPO_MON,
           CANTIDAD,
           CLAVE_ART,
           DESCUENTO,
           DESCUENTO2,
           DESCUENTO3, 
           DESCUENTO4,
           COSTO,
           IEPS,
           IMPUESTO,
           COSTO + IMPUESTO + ISNULL(IEPS, 0) AS TOTAL
      FROM COMPRAS
      JOIN PARTCOMP
        ON COMPRAS.NO_REFEREN = PARTCOMP.NO_REFEREN
      JOIN __input
        ON __input.NumFolio = COMPRAS.NO_REFEREN;
END;

Note that I removed the @ symbol from the parameter name just in case. But maybe it will work with him too.

Also, in case it will help you in the future, it seems that in the most recent versions of Advantage, it is no longer necessary to use this virtual table __input . Rather, you can use the parameter names directly, but adding a _ to the front of the name. So, taking the example above, to access the parameter NumFolio , you could do it using _NumFolio in the query. ( Reference )