Skip to Content
-1

I can't call schema & name procedure from variable

May 10 at 05:34 PM

51

avatar image
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;
1.png (10.3 kB)
2.png (20.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
May 10 at 05:44 PM
2

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

3.png (16.3 kB)
4.png (16.8 kB)
0

What filter you wanna apply to the query?

0