Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 29 Jan 2002 @ 08:56:29 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Using a variable for a table name in Stored Procedure
 
From:   Paul Johnson

The USING clause in a BTEQ has traditionally been used to import variables into an SQL procedure. The main drawback was the inability to directly parameterise the target table name. The workaround is to import the target table name from a host file, export an SQL statement then perform a .RUN on the result.

It goes something like this (untested, and it's b4 9am GMT):

/* The file containing the target table name(s) */
.import file=input.dat

/* Repeat for each record on the input file */
.repeat *

/* The file containing the generated SQL */
.export file=output.sql

/* Generate the SQL */
using tab_name (char(30))
select 'insert ' || :tabname || ' values (1,2,3);';

/* Run the generated SQL */
.run output.sql

We have used this technique recently to generate lengthy update/insert operations on IOS tables within the Teradata CRM application. The name of the target tables is ever-changing, hence the need for parameterisation.

Paul Johnson.



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016