MySQL Forums
Forum List  »  InnoDB

Sql Server trick to avoid cursors - Possible in MySql?
Posted by: Rod Sherer
Date: March 10, 2009 08:09AM

I have used this in Sql Server because cursors are very detrimental to performance, especially when used in triggers. The trick is to take a driving SQL statement which would usually make up the driving loop if using a cursor, and instead have the sql statement create a string of procedure calls (example below). Is it possible to replicate this cursor replacement method in MySql?

SET command_calls = ''
SELECT command_calls = command_calls + 'EXEC my_proc ' + param1 + ',' + param2 + ';'
FROM my_table


Note: The command_calls value could look like...
EXEC my_proc 1,1;EXEC my_proc 1,2;EXEC my_proc 2,1; ... and so on for each row returned by the query.

Also note that the downside to this method is that if the query returns more information than the string variable can capture, you will raise an error on the incomplete command call created at the end of the string, not to mention that you will lose the remainder of needed processing.

Options: ReplyQuote

Written By
Sql Server trick to avoid cursors - Possible in MySql?
March 10, 2009 08:09AM

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.