stored procedures that execute from a select

Database
Enthusiast

stored procedures that execute from a select

Hey Guys,

 

I have a table (say Table_1 with one column) in which a macro will insert the DML statements (insert statements). I need to write a SP to query this table (just select from table_1) and the result of that needs to be executed so that it will do the DML operation...I have tried the below proc, but as i have never written SPs so far, it looks like I cant get it to work...any help is appreciated!

 

replace PROCEDURE new_proc_test (OUT test_stmt varchar(4000))

BEGIN

DECLARE SqlStr VARCHAR(4000);

DECLARE C1 CURSOR FOR S1;

SET SqlStr = 'sel * from table_1;';

PREPARE S1 FROM SqlStr;

OPEN C1;

FETCH C1 INTO test_stmt; ----this is where i need to execute the step instead of simply fetching it (call the dbc.execsql or execute immediate which doesnt work

CLOSE C1;

END


Accepted Solutions
Teradata Employee

Re: stored procedures that execute from a select

The general idea could be something like this:

BEGIN

DECLARE SqlStr VARCHAR(4000);

FOR C1 AS SELECT SqlTxtCol FROM TABLE_1 DO

  SET SqlStr = C1.SqlTxtCol;

  EXECUTE IMMEDIATE :SqlStr;

END FOR;

END

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: stored procedures that execute from a select

If test_stmt contains the name of a macro, then I think you have to add yet another cursor in you declarations like

       DECLARE M1 CURSOR FOR Macro1;

then after the Fetch do something like

       set :test_stmt = 'Exec ' || :test_stmt;
       PREPARE Macro1 from :test_stmt;
       OPEN M1;

Teradata Employee

Re: stored procedures that execute from a select

The general idea could be something like this:

BEGIN

DECLARE SqlStr VARCHAR(4000);

FOR C1 AS SELECT SqlTxtCol FROM TABLE_1 DO

  SET SqlStr = C1.SqlTxtCol;

  EXECUTE IMMEDIATE :SqlStr;

END FOR;

END

Enthusiast

Re: stored procedures that execute from a select

Thanks so much...that worked like a charm!