MySQL Forums
Forum List  »  Stored Procedures

No Dynamic SQL in Function or Trigger Frustration
Posted by: Mike Jonsson
Date: February 27, 2014 06:47PM

I must say this is one of the undoubtfully most frustrating facts of MySQL.
Has this been outdated by an upgrade or is the limitation still available in the newer than 5.5 versions of MySQL?

I have just completed the tedious and extremely confusing creation of a column/row wise comparison and validation routine which MUST be defined as a function at all costs.

I doubt very much I will get ANY form of acceptable performance in this though.

---

The base tables for my query applies a combination of tables. 4 to be precise, using 5 columns in each to set 20 possible values.

For each string of 20 I intend to account for spreads and statistical offsets between the various numbers when applied as a matrice.

In order to achieve this I have to run this test function inside the SELECT statement of my main query and as such it is impossible to call up the validation resulting in a tinyint(3) from a procedure.

The function now created to achieve this step-by-step validation only for ONE single returned column is a whopping 317 Lines of Code...

:< To return a 1 or 0 ???

If Dynamic SQL in a function was allowed I could have used a MEMORY Engine based table to do this in approx. 12 lines of code!

Imagine the difference between applying some 19000*64000*54500*11900 table rows of a query on running through 312 lines of perplexing IF statements to make up for not being able to use dynamic sql?

My questions are then finally...

1. Is there a version of MySQL ready made that allows this?

2. Are there any plans in motion to mitigate this lack of functionality

...or will I have to move to ASM programming to get some speed into this on a standalone program?

Options: ReplyQuote


Subject
Views
Written By
Posted
No Dynamic SQL in Function or Trigger Frustration
2574
February 27, 2014 06:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.