APPENDING A DYNAMIC VALUE ON A TABLE IN INSERT STATEMENT IN STORED PROCEDURE

General
Enthusiast

APPENDING A DYNAMIC VALUE ON A TABLE IN INSERT STATEMENT IN STORED PROCEDURE

REPLACE Sample_Proc

(

  PRODUCT_NAME VARCHAR(20)

)

BEGIN

DECLARE vVar1 INTEGER;

/*OPERATION1*/

SET vVar1= 'VARIABLE'||PRODUCT_NAME

/*OPERATION 2*/

INSERT INTO LOG_Table||PRODUCT_NAME

SELECT 1,'Success'

END;

/*CALLING THE PROCEDURE*/

CALL Sample_Proc('ABCD');

Here in the above procedure i am trying to pass PRODUCT NAME Dynamically in the call statement so as to insert into the LOG_TABLE based on the value. On doing this i get a syntax error. Operation 1 as shown above doesnt throw error however Operation 2 throws a syntax error as below. Please help me.

ERROR:

Missing/Invalid SQL statement'E(3707):Syntax error, expected something like a 'SELECT' keyword or a 'NONTEMPORAL' keyword or 'AS' keyword between the word 'LOG_TABLE' and '"||"'.'.

--Arun

Tags (1)
3 REPLIES
Teradata Employee

Re: APPENDING A DYNAMIC VALUE ON A TABLE IN INSERT STATEMENT IN STORED PROCEDURE

You need to use dynamic sql.

Something like this:

SET v_sql_stmt =  'INSERT INTO LOG_Table'|| 'PRODUCT_NAME' ||' SELECT 1,''Success'';'

CALL DBC.SYSEXECSQL(v_sql_stmt);
Enthusiast

Re: APPENDING A DYNAMIC VALUE ON A TABLE IN INSERT STATEMENT IN STORED PROCEDURE

Hi

    Thanks for the reply. But when i am able to append the dynamic value without DBC.SYSEXECSQL in Operation 1 as above why is it not possible to achieve the same in Operation 2. Does Teradata have some limitation?

--Arun

Teradata Employee

Re: APPENDING A DYNAMIC VALUE ON A TABLE IN INSERT STATEMENT IN STORED PROCEDURE

Yes it is a limitation. You can't do that with an inline sql statement. There are some different ways to handle dynamic sql. Do you have the stored procedures and embedded SQL manual?

You can download it below. Go to www.info.teradata.com and search if you have particular version of Teradata that you would like to find the related manual for.

http://www.info.teradata.com/edownload.cfm?itemid=131540022