CODEWITHSUNDEEP
mysqlnode.jsstored-proceduresstored-functions
user3355182
user3355182

Reputation: 151

mysql stored function calling dynamic stored procedure

I have a stored procedure that concatenates sql to make insert/update etc. The stored procedure works. However, I dont like the way its called (its OT but Im using node.js and calling a stored procedure is quite hard to accomplish and there doesnt seem to be much support/documentation for this).

So I decide to create a "simple" mysql stored function that will basically call the stored procedure (the function is called with a few IN parameters, the procedure (from the function) is called with the same IN parameters PLUS the OUT parameter ).

However, i get an error:

Dynamic SQL is not allowed in stored function or trigger

yes, I have read about functions and dynamic sql isnt allowed. BUT: Im doing dynamic sql from the procedure, not from the function. But still mysql doesnt like it. Is there a solution to this? I dont think something is wrong with my code...

CREATE FUNCTION my_func(a varchar(50), b varchar(50), c varchar(50), d integer, e integer)
RETURNS INT(10)
DETERMINISTIC
BEGIN
set @theid = 0;
call my_proc(a, b, c, d, e, @theid);
RETURN (@theid);

Upvotes: 0

Views: 1273

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562498

I can't confirm this right now in the code, but I would assume that there are some data structures associated with prepared queries that can exist only one at a time in a given MySQL thread. Since a stored function or trigger is invoked from a query (which may be dynamic SQL itself), the function or trigger can't initiate a new dynamic SQL query.

So it doesn't matter that the dynamic SQL is in a procedure. You can't make a stored function that prepares and executes dynamic SQL, whether directory or indirectly.

You might be trying to find a workaround for a solved problem. See Is there a driver for mysql on nodejs that supports stored procedures?

Upvotes: 1

spencer7593
spencer7593

Reputation: 108430

Q: But still MySQL doesnt like it. Is there a solution to this?

No.

The "Dynamic SQL is not allowed in a function" restriction also extends to any stored procedures called by the function.

There is no solution. There is no workaround.

This simply can't be done: you can't use Dynamic SQL in the context of a function or trigger.

Upvotes: 1

Related Questions