Dynamic sql

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Visitor

Dynamic sql

Hi I have a question about dynamic sql

I have query

select  'select date as czas_przeliczenia,databasename,tablename,(select count(*) from ' ||trim(databasename)||'.'||trim(tablename)|| ' ) from dbc.tables where databasename=' || ''''||trim(databasename)||'''' || 'and tablename ='||''''||trim(tablename)||'''' from dbc.tables where trim(databasename) ='DB_01'

 

After running this query I have result with query .

And nextly when i run my query I have result about counting.

 

But I want procedure whose make this automatically.

 

I create dynamic procedure but in this procedure I havent use variable=variable.

Maybe I should use two coursor?

 

This is my one of many script

 

REPLACE PROCEDURE db_utrzymanie.TEST_SP1( baza varchar(200))
DYNAMIC RESULT SETS 1
BEGIN

DECLARE SQL1 VARCHAR(100);
DECLARE crsr CURSOR WITH RETURN ONLY FOR stmt;

--SET SQL1 = 'SELECT * FROM DB_utrzymanie.MUP_PROCESSES; ' ;
SET SQL1=  'select  'select date as czas_przeliczenia,databasename,tablename,(select count(*) from ' ||trim(databasename)||'.'||trim(tablename)|| ' ) from dbc.tables where databasename=' || ''''||trim(databasename)||'''' || 'and tablename ='||''''||trim(tablename)||'''' from dbc.tables where trim(databasename) ='DB_META_GLUT''''

PREPARE stmt FROM SQL1;
OPEN crsr;

END

 

 

Please help me

 

1 REPLY
Junior Contributor

Re: Dynamic sql

Following threads show how to run multiple counts in an SP using a cursor:

Stored procedures

Port MS SQL Cursor to Teradata