CODEWITHSUNDEEP
stored-proceduresamazon-redshiftplpgsql
recklessguyyy
recklessguyyy

Reputation: 3

how to pass schema_name_variable in stored procedure syntax "call <<schema_name_variable>>.storedproceduretask()

I am trying to pass the schema name as a variable in the stored procedure syntax (CALL schema_name_var.sp_master_procedure();-> full code given below). But this code fails when I try to run in redshift. Could somebody provide a solution on how can I achieve this?

CREATE OR REPLACE PROCEDURE master_work_se.sp_master_table_automation()
    LANGUAGE plpgsql
    AS '
    DECLARE
      schema_name_var nvarchar(max) = 'master_work_se';

        BEGIN
          CALL schema_name_var.sp_master_procedure();
        END;

    END;
    ';

Upvotes: 0

Views: 529

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247575

You need dynamic SQL:

EXECUTE format('CALL %I.sp_master_procedure()', schema_name_var);

Upvotes: 1

Related Questions