Skip navigation
13128 Views 1 Reply Latest reply: Jun 23, 2010 7:32 AM by AmbarishDon RSS
John D Townley New Enzee 3 posts since
Jun 10, 2010
Currently Being Moderated

Jun 22, 2010 1:16 PM

Question on dynamic call within a "for row in select .... loop"

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?



 

 

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points