All Forums Database
Ansh 49 posts Joined 10/08
12 Jun 2009
Using Dynamic Sql Stored Procedure

Hi ,I am trying to write a dynamic sql using Stored procedure wherein I am using the underlying table as one of the input parameters.Below is the SP which I am trying to create but I am facing Invalid Sql error.CREATE PROCEDURE Proc_Dynamic (X INTEGER,Y DATE,MYTABLE VARCHAR(30))BEGINCALL DBC.SysExecSQL('INSERT INTO Databasename|| '.' || :MYTABLE )SELECT A, ,SUM(B) ,SUM(C)FROM Databasename.tablename2GROUP BY 1,2,3,4,5,6,7,8,9,10;end;Could anyone tell me whether we can use MYTABLE in above example as I am trying to do ?Is there any other method in Teradata wherein we can dynamically define the tablename?Thanks,Ansh

Ansh 49 posts Joined 10/08
12 Jun 2009

Just correcting the SP :CREATE PROCEDURE Proc_Dynamic (IN X INTEGER,IN Y DATE,IN MYTABLE VARCHAR(30))BEGINCALL DBC.SysExecSQL('INSERT INTO Databasename'|| '.' || :MYTABLE ||'SELECT' ||A,B,C,SUM(D),SUM(E)||'FROMDatabasename.tablename2GROUP BY 1,2 ;'end;

Ansh 49 posts Joined 10/08
15 Jun 2009

Can anybody please reply to this ?I need this info as early as possible..Thanks in Advance !!!

Adeel Chaudhry 773 posts Joined 04/08
15 Jun 2009

Hello,Four issues:1. Incomplete GROUP BY clause2. Invalid concatenation for columns3. Right-Parenthesis missing for SysExecSQL4. Semi-colon missing with CALL statementTry following:CREATE PROCEDURE Proc_Dynamic (IN X INTEGER, IN Y DATE, IN MYTABLE VARCHAR(30))BEGINCALL DBC.SysExecSQL('INSERT INTO Databasename.' || :MYTABLE || ' SELECT A, B, C, SUM(D), SUM(E) FROM Databasename.tablename2 GROUP BY 1,2,3');END;HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Ansh 49 posts Joined 10/08
15 Jun 2009

Hi Adeel,thanks for your reply.But the simple query like below is also giving me the same error.COuld you be ablt to help ?Replace PROCEDURE DATABASENAME.Proc_Dynamic (IN X INTEGER, IN Y DATE, IN MYTABLE VARCHAR(30))BEGINCALL DBC.SysExecSQL('INSERT INTO DATABASENAME.' || : MYTABLE|| ' SELECT * from DATABASENAME.TABLENAME2');END;

Adeel Chaudhry 773 posts Joined 04/08
15 Jun 2009

I believe, if you could provide the exact SP code and exact error, that would be of great help! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.