All Forums Database
barni07 8 posts Joined 02/06
30 Oct 2006
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

Fred 1096 posts Joined 08/04
31 Oct 2006

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).

Manika Agrawal 1 post Joined 05/11
12 May 2011

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?

12 May 2011

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

egagan 3 posts Joined 03/11
16 May 2011

+ 1 Manika
i am facing the same issue

dnoeth 4628 posts Joined 11/04
18 May 2011

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

Dieter

You must sign in to leave a comment.