All Forums Database
chinmay_uce02 9 posts Joined 07/07
28 Sep 2007
What is dynamic sql

HI can any one explain me what is dynamic sql in teredata.is it the sql we write and run in sql assistant or any thing different?thanks in advanceChinmay

Regards, Chinmay
famalau 43 posts Joined 08/07
01 Oct 2007

Chinmay,Dynamic SQL is a concept that allows you to compose your SQL code "on the fly". I have used it inside stored procedures, although it allowed to be used with Embedded SQL as well. I used it to be able to inform a database name and a table name as parameters to a stored procedure, so I could dynamically use a different database and table, depending upon those informed parameters.I hope this helps.

Regards,

Fabio

chinmay_uce02 9 posts Joined 07/07
03 Oct 2007

Thanks a lot.

Regards,
Chinmay

hurstrescue 9 posts Joined 09/07
10 Oct 2007

Can you please post an example?Thanks,Joe

joedsilva 505 posts Joined 07/05
10 Oct 2007

REPLACE PROCEDURE HARDDEL(IN TBLNAME VARCHAR(30))BEGIN CALL DBC.SYSEXECSQL('DELETE FROM ' || TBLNAME || ' WHERE REC_ERR IS NOT NULL ;');ENDThe above procedure is an example for the use of dynamic SQL.the procedure accepts a tablename, and deletes all records in it which are having a value for REC_ERR column. (It's assumed that any table name passed as an argument will have the REC_ERR column defined).example on invocation.CALL HARDDEL('CUST_INFO'); -- deletes recs from CUST_INFO tableCALL HARDDEL('SUPPLIER_INFO'); -- deletes recs from SUPPLIER_INFO table

KS42982 137 posts Joined 12/12
17 Dec 2012

Hi, I am using dynamic SQL to do the update. I face difficulty when I want to filter my query using any string.
For example - where attribute1 = 'ABC' - It doesn't allow that and consider ABC as a column name. I did lot of research and tried vairous methods like ' "ABC" (with double quotes), '"ABC"' (with one single and one double quote) - But nothing works.
Can anyone please help me out ?
Thanks.

Harpreet Singh 101 posts Joined 10/11
17 Dec 2012

use two single quote on each side as below
=''ABC''
 
HTH
Harpreet

KS42982 137 posts Joined 12/12
19 Dec 2012

That Helped. Thanks.
One more question, I need to pass string as parameter and need to use in WHERE clause. For example in a dynamic SQL,
'SELECT * FROM DBC.COLUMNS WHERE TABLENAME =' || TB1 || .. ;
The above doesn't work as say when I pass TB1= 'SRC_TB' - it doesn't go as 'SRC_TB'. It goes as SRC_TB (without any quotes) and that throws an error. I tried few options like below but nothing worked.
'SELECT * FROM DBC.COLUMNS WHERE TABLENAME =' || ''TB1'' || ... ;
'SELECT * FROM DBC.COLUMNS WHERE TABLENAME =' || '' || TB1 || '' || .... ;
Any suggestions ?
Thanks.

Harpreet Singh 101 posts Joined 10/11
19 Dec 2012

Use
'select * from dbc.columns where tablename = '''||TB1||''' ; '
Three ' single quotes before ||TB1 and similarly end
HTH
Harpreet

KS42982 137 posts Joined 12/12
19 Dec 2012

It worked very well. Thank you.
 

You must sign in to leave a comment.