Using Dynamic Sql Stored Procedure

Database
Enthusiast

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))
BEGIN
CALL DBC.SysExecSQL('INSERT INTO Databasename|| '.' || :MYTABLE )
SELECT
A,
,SUM(B)
,SUM(C)
FROM

Databasename.tablename2

GROUP 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
5 REPLIES
Enthusiast

Re: Using Dynamic Sql Stored Procedure

Just correcting the SP :

CREATE PROCEDURE Proc_Dynamic (IN X INTEGER,IN Y DATE,IN MYTABLE VARCHAR(30))
BEGIN
CALL DBC.SysExecSQL('INSERT INTO Databasename'|| '.' || :MYTABLE ||
'SELECT' ||
A,
B,
C,
SUM(D),
SUM(E)||
'FROM
Databasename.tablename2
GROUP BY 1,2 ;'
end;
Enthusiast

Re: Using Dynamic Sql Stored Procedure

Can anybody please reply to this ?
I need this info as early as possible..

Thanks in Advance !!!

Teradata Employee

Re: Using Dynamic Sql Stored Procedure

Hello,

Four issues:

1. Incomplete GROUP BY clause
2. Invalid concatenation for columns
3. Right-Parenthesis missing for SysExecSQL
4. Semi-colon missing with CALL statement

Try following:

CREATE PROCEDURE Proc_Dynamic (IN X INTEGER, IN Y DATE, IN MYTABLE VARCHAR(30))
BEGIN
CALL 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
Enthusiast

Re: Using Dynamic Sql Stored Procedure

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))
BEGIN
CALL DBC.SysExecSQL('INSERT INTO DATABASENAME.' || : MYTABLE||
' SELECT * from DATABASENAME.TABLENAME2');
END;
Teradata Employee

Re: Using Dynamic Sql Stored Procedure

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