Prepare statement in a stored procedure

Database
Enthusiast

Prepare statement in a stored procedure

Hello.

Do you know if executing a prepared sql in a stored procedure is possible? I mean, I'd like to declare a new variable, compose a sql statement in that variable, and then execute it. That would be great for me now...

Thanks in advance
5 REPLIES
Teradata Employee

Re: Prepare statement in a stored procedure

You can't do PREPARE but you can execute a text string as a dynamic SQL statement via CALL DBC.SysExecSQL(). There are restrictions on what SQL statements are allowed, but it may satisfy your requirement. See "Using Dynamic SQL in Stored Procedures" in the manual (SQL Reference / Stored Procedures and Embedded SQL).

Re: Prepare statement in a stored procedure

I am facing an issue while preparing a SQL from a variable(sql_str) and then executing it. My query contains unicode data.

SET sql_str = 'SELECT customer_id from test.abc where customer_name = '深圳市酷动数码有限公司';';
PREPARE QUERY FROM sql_str;
OPEN result_set;

I believe the procedure is not able to prepare dynamic result set for query containing unicode data.
Any pointers regarding this?
Enthusiast

Re: Prepare statement in a stored procedure

Thanks for the tip. I have made a lot of progress
Fan

Re: Prepare statement in a stored procedure

+ 1 Manika
i am facing the same issue
Senior Apprentice

Re: Prepare statement in a stored procedure

Unicode shouldn't be any problem, could you be more specific about "an issue" or share the full source code?

In Manika's sql_str the single quotes should be doubled:
'SELECT customer_id from test.abc where customer_name = ''深圳市酷动数码有限公司'';';

But this is not specific to an SP, it's Standard SQL.

Dieter