Execute statement through procedure

General
Highlighted
Enthusiast

Execute statement through procedure

I want to execute below statement through procedure and return the definition of objects .
I know its easy with macro but I want through procedure as I need include other statement also
select case when when tablekind = 't' then 'show table '

when tablekind = 'v' then 'show view '

end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')

from dbc.tables tbl_view

where tbl_view.tablekind in('t','v')

AND DATABASENAME IN

(

'db1','db2'

)

and trim(tablename) like any ('tb1%','tb2%')
5 REPLIES 5
Teradata Employee

Re: Execute statement through procedure

SHOW is not permitted within stored procedures.

Teradata Employee

Re: Execute statement through procedure

If you're prepared to dive into some External Stored Procedures (XSP), you can issue a SHOW dynamically.

 

Check out the link below for details and some sample code on how to do it using some relatively simple Java code.

Running unsupported queries from a Stored Procedure

 

regards,

Paul

 

P.S. Note the disclaimer on the linked page about supportability. i.e. it has worked in tests may not be formally supported by Teradata.

Tags (1)
Enthusiast

Re: Execute statement through procedure

Thanks Fred , good to know that so is there any way we can return the statement only I mean return value like
Show procedure procedure_name ;

I tried created with some dynamic and other syntax but not working..could you please share one sample script that would be really helpful..
Thanks in advance..
Teradata Employee

Re: Execute statement through procedure

I won't claim to be Fred, but I think you may be after something like the following macro (you can do this in a stored procedure, but I had this code handy, so if it helps):

 

replace macro <MY_DB>.GetShowDBObjects
(
   i_DatabaseNames varchar(1000)
  ,i_TableNames    varchar(1000)
)
as
(
    select 
        case when tbl.tablekind = 'T' then 'show table '
             when tbl.tablekind = 'V' then 'show view '
        end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')
     from DBC.TablesV tbl

    inner join (   
                    select trim(o_token) as fq_pattern
                      from table (
                                  strtok_split_to_table ( 1   ,trim(:i_databaseNames) ,',')
                                  returns ( o_Key    integer
                                           ,o_tokNum integer
                                           ,o_token  varchar(257) character set unicode)
                    ) as tf_Token
                ) as db_list
        on tbl.databasename like db_list.fq_pattern escape '\'
     inner join (   
                    select trim(o_token) as fq_pattern
                      from table (
                                  strtok_split_to_table ( 1   ,trim(:i_tablenames) ,',')
                                  returns ( o_Key    integer
                                           ,o_tokNum integer
                                           ,o_token  varchar(257) character set unicode)
                    ) as tf_Token
                ) as tbl_list
        on tbl.tablename like tbl_list.fq_pattern escape '\'

    where tbl.tablekind in ('T','O','V') 
     order by  case when Tablekind = 'T' then 1
                    else 2 
                end
    ;
);

This will mean you can call the macro as follows:

e.g.1. To get a list of show statement for all ('%') tables and views that are in Databases with names like 'DBC' or 'E%':

exec <MyDB>.GetShowDBObjects('DBC,E%','%');

e.g.2.To get a list of show statement for tables (including NOPI tables) and views that are in Databases with names like 'DBC' or 'E_%' and that have names that start with 'D' or 'I':

exec <MyDB>.GetShowDBObjects('DBC,E\_%','D%,I%');

Hope that helps!

 

regards,

Paul

(still not Fred Smiley Very Happy)

Teradata Employee

Re: Execute statement through procedure

If you want to return the results of a dynamic SQL statement, you must

  • Specify DYNAMIC RESULT SETS n clause in the definition
  • Declare a CURSOR for the prepared statement WITH RETURN
  • PREPARE the SQL text to initialize the prepared statement
  • OPEN the cursor, and leave it open when you exit the procedure

CREATE PROCEDURE ...

... DYNAMIC RESULT SETS 1 ...

BEGIN

DECLARE SqlString VARCHAR(1024);

DECLARE csr CURSOR WITH RETURN FOR pstmt;

/* Create the SQL statement string */

PREPARE pstmt FROM SqlString;

OPEN csr;

END;

 

If you actually are building answer set rows via procedure logic rather than wanting to directly return results from a SQL statement, use a Global Temporary table. Have the procedure insert the rows into (a materialized instance of) the GTT and then use a CURSOR WITH RETURN to pass the contents of the GTT back to the caller.