How to run queries dynamically in the procedure wchic are stored in table.

Database

How to run queries dynamically in the procedure wchic are stored in table.

Hi Guru's,

I have a requirement to call the queries dynamically with in a stored procedre which are stored in a table.

The procedure should select each query from the table and insert the data into one global temp table.

Is it possible in teradata procedures, if so should  i use dynamic sql or cursors.

eg:  Rules table has below data.

   validation  validaion_name  txt_message                  query 

     1          verify nulls    some text     Sel * from abc.table where columnname is null

     2          verify spaces   some text     Sel * from abc.table where coumnname=' ' 

     3          verify value      text        Sel * from abc.table where columnname=2000;

     "               "             "                         "

     "               "             "                         "

     "               "             "                         "

Can someone please advice how to do this.

Sankar

2 REPLIES
WAQ
Enthusiast

Re: How to run queries dynamically in the procedure wchic are stored in table.

Try DBC.SYSEXECSQL. I think this will solve your problem.

Senior Supporter

Re: How to run queries dynamically in the procedure wchic are stored in table.

You can build a stored procedure to do that. Within the SP you would need to define a cursor to read the SQL and use DBC.SYSEXECSQL to execute the SQL. You would also need to take care of exception handling and logging etc.

You can also export the SQLs into a file and run the file within a BTEQ script. 

check Jimms comment in 

http://forums.teradata.com/forum/tools/bteq-export-issue

as an example.