All Forums Database
sankar_p 1 post Joined 07/12
06 Jul 2012
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

 

WAQ 158 posts Joined 02/10
06 Jul 2012

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

ulrich 816 posts Joined 09/09
06 Jul 2012

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. 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.