Jun 22, 2010 1:16 PM
Question on dynamic call within a "for row in select .... loop"
-
Like (0)
I am trying to create a procedure that has a for v_row in select..... loop .... end loop; logic and i want to be able to dynamically use a different table each time it is called
so...for example this procedure
CREATE OR REPLACE PROCEDURE DEV_ONLN_USER.JTTEST(CHARACTER VARYING(ANY))
CREATE OR REPLACE PROCEDURE DEV_ONLN_USER.JTTEST(CHARACTER VARYING (ANY))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
declare
p_input_table ALIAS for $1;
v_sql varchar(600);
v_result_table varchar(60);
v_table varchar(60);
v_row record;
v_result_table_tmp varchar(60);
r record;
begin
v_table :=p_input_table;
for v_row in select a.component_id from
devontxn..hhid_table e a
where a.is_active='Y' order by a.component_id
loop
RAISE NOTICE 'CALL %', v_row.component_id;
end loop;
end;
END_PROC;
i would like to use a procedure like this, but instead i would like to dynamically replace the select of the table devontxn..hhid_table with
a table i passed p_input_table instead.
I dont see any kind of examples of this in netezza documentation....does anyone know how to dynamically change the table name in a for v_row in select ...loop?
Hi,
You can create dynamic query like below,
dynamic_sql := 'select a.component_id from ' || v_table|| ' a where a.is_active='Y' order by a.component_id';
for v_row in execute dynamic_sql
loop
RAISE NOTICE 'CALL %', v_row.component_id;
end loop;
-AmbarishDon

