What is dynamic sql

Database

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 advance
Chinmay
9 REPLIES

Re: What is dynamic sql

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.

Re: What is dynamic sql

Thanks a lot.

Re: What is dynamic sql

Can you please post an example?

Thanks,

Joe

Re: What is dynamic sql

REPLACE PROCEDURE HARDDEL(IN TBLNAME VARCHAR(30))
BEGIN

CALL DBC.SYSEXECSQL('DELETE FROM ' || TBLNAME || ' WHERE REC_ERR IS NOT NULL ;');

END

The 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 table

CALL HARDDEL('SUPPLIER_INFO'); -- deletes recs from SUPPLIER_INFO table

Re: What is dynamic sql

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.

Re: What is dynamic sql

use two single quote on each side as below

=''ABC''

HTH

Harpreet

Re: What is dynamic sql

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.

Re: What is dynamic sql

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

Re: What is dynamic sql

It worked very well. Thank you.