Passing a table name as a variable

Database

Passing a table name as a variable

Hi!

 

Can TD accommodate something a long the lines of this? We have a lot of snapshot tables and i would like to make it a little easier running multiple months. Thank you!

 

declare tablevar varchar

set tablevar = table

 

select * 

from DB.tablevar

6 REPLIES
Senior Apprentice

Re: Passing a table name as a variable

Hi,

 

If you want the dbms to handle this then you need to use a stored procedure (SP).

The table name is a parameter to the SP.

Within the SP you use dynamic sql which is built to incorporate the supplied parameter(s).

 

Outside of the dbms you could do this using scripting or programming.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Passing a table name as a variable

In a stored procedure:
declare tablevar varchar(256);
declare tablesel varchar(512);
set tablevar = 'MyTable'; -- or :some_other_variable
set tablesel = 'select * from DB.' || tablevar;
execute immediate tablesel;

or more simply:
declare selstmt varchar(512);
set selstmt = 'select * from DB.' || <the-name-of-the-table>;
execute immediate selstmt;

In a shell script:
TABLE=<whatever>
...
bteq <<EOF
...
select * from DB.$TABLE;
...
EOF

Re: Passing a table name as a variable

Thank you! Now is there a way to run that in an editor window without it running within an SP?

Teradata Employee

Re: Passing a table name as a variable

Which editor? Most editors can execute shell scripts, and you can create an editor macro that repeatedly edits a shell script such as the above example and executes it.  But it seems to me it would be easier to write another shell script that does the same thing using perhaps sed or awk.

Re: Passing a table name as a variable

Sorry I am unfamiliar with shell scripts. I am using Toad DataPoint to connect to our TD environment. 

Teradata Employee

Re: Passing a table name as a variable

This sounds like a question for Toad then....