I can't call schema & name procedure from variable

[Former Member]

My Prodecure's schema and name are stored in a colum of table. In another procedure I put schema and name in variable, but I don't now How to call procedure from variable. Help Me

The table is:

SELECT NOMBRE_PROCEDURE FROM ETL_CONTROL.ALL_PROCEDURES WHERE CODIGO_PROCEDURE=1;

The anoter stored procedure is:

I can't to call procedure because the procedure's name is into a variable 😞 Please Help Me!

CREATE PROCEDURE "P1942494358"."LLAMA_PROCEDURE2"()
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA P1942494358
AS
BEGIN
DECLARE PROCEDURE1 varchar(200);

SELECT NOMBRE_PROCEDURE into PROCEDURE1 FROM ETL_CONTROL.ALL_PROCEDURES 
WHERE CODIGO_PROCEDURE=1;

CALL :PROCEDURE1||; --ERROR
--CALL ||:PROCEDURE1||; --ERROR
--CALL 'ETL_CONTROL.SP_CARACT'()';--ERROR
END;

[pfefferf · Accepted solution]

The call statement cannot interprete procedure names in a dynamic way (e.g. from variables). For that you have to use Dynamic SQL.

For instance:

CREATE PROCEDURE "P1942494358"."LLAMA_PROCEDURE2"()
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA P1942494358
AS
BEGIN
DECLARE PROCEDURE1 varchar(200);
DECLARE dynamic_statement nvarchar(255);

SELECT NOMBRE_PROCEDURE into PROCEDURE1 FROM ETL_CONTROL.ALL_PROCEDURES 
WHERE CODIGO_PROCEDURE=1;

dynamic_statement := 'CALL ' || :PROCEDURE1 || '();';

EXEC :dynamic_statement;

END;

Regards,
Florian

[Former Member · Accepted solution]

Thanks Florian. Is correct!! florian.pfeffer One question more please

In this table I have for example 5 procedure's names:

SELECT * FROM "ETL_CONTROL"."PRUEBA_INSERCIONPROCEDURES_DOS";

With this procedure and internt cursor. I call all procedures in order.

CREATE PROCEDURE "P1942494358"."MALLA_PROCEDURES_CAMPOSOL_GINO" (
        IN fecha_inicio DATE,
		IN fecha_fin DATE
		)
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
BEGIN
/*****************************
    Write your procedure logic
 *****************************/
 DECLARE v_StrProcedure VARCHAR(200);
 DECLARE dynamic_statement nvarchar(255);
 DECLARE CURSOR c_products FOR
 SELECT CODIGO_PROCEDURE,NOMBRE_PROCEDURE 
        FROM "ETL_CONTROL"."PRUEBA_INSERCIONPROCEDURES_DOS";
FOR cur_row as c_products DO
    
	v_StrProcedure := cur_row.NOMBRE_PROCEDURE;


dynamic_statement := 'CALL ' || :v_StrProcedure || '();';
EXEC :dynamic_statement;
END FOR;
END;

But, there are some(procedures) that need parameters (fecha_inicio, fecha_fin) (for example procedures 2 and 3 (column CODIGO_PROCEDURE from TABLE "ETL_CONTROL"."PRUEBA_INSERCIONPROCEDURES_DOS"))

Is possible put a filter inside the cursor only for some procedures stored in the table "PRUEBA_INSERCIONPROCEDURES_DOS" for this s requirement?

Please, could you help me?

Thanks for your kindness.

Gino Alfaro

[pfefferf · Accepted solution]

What filter you wanna apply to the query?