Harcoding the Cursor values in Dynamic sql!

General
rdx
N/A

Harcoding the Cursor values in Dynamic sql!

Hi Gurus,

Am trying to implement to find the automated data skewness on columns of perticular table.

Here am trying to cretae a PROC with CURSOR.

Am trying to insert values into one GTT using dynamic sql concept.

can anybody please let me know how to hardocde the cursor values in dynamic sql?

for ex: below select is available in dynamic sql that pasted below.

Here want to hardocde the databasename,tablename,columnname for everyrow that fetchedby cursor.

SEL TOP 5 ' || loopvar.databasename || ',' || loopvar.tablename || ',' || loopvar.columnname || ',a.*

 ORDER BY CNT DESC

 FROM

-------

When i keep single quotes around the loopvar.databasename (like 'loopvar.databasename'), TD is just trhowing error like

loopvar object doenot exist.

Referred TD STORED PROC DOCS, but couldnt get much info on this topic.

So Please shed some light on this and help me out.

Procedure:

--------------

CREATE PROCEDURE SP_CUR_DYNMIC(IN tblnme VARCHAR(30))

BEGIN

 DECLARE V_SQLSTR VARCHAR(6000);

 DECLARE loopvar_DatabaseName VARCHAR(30);

 DECLARE loopvar_TableName VARCHAR(30);

 DECLARE loopvar_colnname VARCHAR(30);

 /*Declaration of cursor*/

 FOR loopvar AS cur1 CURSOR FOR

 SELECT TOP 1 databasename,tablename,columnname

 FROM dbc.COLUMNSv

 WHERE  databasename = 'XXXX'

 AND tablename = :tblnme

 DO

CALL DBC.SYSEXECSQL(' INSERT INTO testdb.TABLE_SKEW

                      (databasename,tablename,columnname,COLUMNVALUES,Cnt)                                                  

 SEL TOP 5 ' || loopvar.databasename || ',' || loopvar.tablename || ',' || loopvar.columnname || ',a.*

 ORDER BY CNT DESC

 FROM

 (SEL  ' ||loopvar.columnname|| ', COUNT(*) AS CNT

   FROM XXXX.'||:tblnme||' GROUP BY 1) a');  

END FOR;

END;

Thanks in advance.

Tags (1)