Skip to Content
avatar image
-1
Former Member

I can't call schema & name procedure from variable

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 10 at 05:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded