is it possible to display contents of a table through stored procedure?

Tools & Utilities
Enthusiast

is it possible to display contents of a table through stored procedure?

Hello everyone,
is it is it possible to display contents of a table through stored procedure?If yes then can some one provide me with the syntax

appreciate your help

Thanks
Regards
3 REPLIES
Enthusiast

Re: is it possible to display contents of a table through stored procedure?

I need to pass the table name as a parameter to the stored procedure
Teradata Employee

Re: is it possible to display contents of a table through stored procedure?

No you cannot do this (display the results), but you can create the table in the SP. I've heard V2R7 will have the ability to display the results but I don't know that for sure.

The standard way to do this:

Use the SP to populate a volatile table, then, maintaining the connection, read the table. When the connection closes the volatile table will be dropped.

To create the table using dynamic sql you must be the immediate owner and the creator of the sp (means sp in your own user database). Also, you must have permission to run dynamic SQL.

Hope this helps.

Teradata Employee

Re: is it possible to display contents of a table through stored procedure?

If the structure of the "returned" table is always the same, Global Temporary tables are easier to use for this purpose than Volatile tables. If the structure varies,
CREATE VOLATILE TABLE ... AS (SELECT ..) ...
may be useful.

If the tablename is a parameter, you'll need to build the SQL statement as a text string and use dynamic SQL [CALL DBC.SysExecSQL(...)], in which case there are some additional restrictions - notably that the creator of the stored procedure must also be the owner of the procedure; e.g. User1 issues "CREATE PROCEDURE User1.MySP ...".