Saturday, November 05, 2005

Every now and then, people are puzzled by the precise status and extent of MySQL support for dynamic SQL.

Statement Handling

MySQL support the prepared statement syntax. For the better part, a prepared statement is much like a 'normal', immediate statement. The main difference is seen in the way the statement is processed by the server.

Immediate Statements

When an immediate statement is issued, it is processed directly. Processing comprises the following steps:

Parsing: lexical and syntactic analysis of the statement

Planning: optimizer devises a strategy to realise the required result or action, the execution plan or query plan

Execution: retrieval/seeking, writing and reading of data and, if applicable, the construction of a resultset

After these steps, the server responds to the request by sending the client a resultset (if applicable), or an acknowledgement that the statement was executed. Of course, all these actions are performed in concert, and the client is not aware of these different steps taking place. This becomes clear when typing the following statement into the MySQL command line client tool:

This time, we didn't get a resultset.The PREPARE statement instructs the server to parse the query, and possibly, to devise the execution plan. PREPARE associates an identifier with the statement, stmt, wich acts as a handle to refer to the statement and the corresponding execution plan.

Actual execution is postponed until called for by the EXECUTE statement, using the handle to identify the prepared statment to execute.

This actually touches upon the major purpose of the prepared statement concept: when a statement is to be repeatedly executed, a prepared statement is potentially more efficient. Because only the execution step needs to be repeated, there is less time wasted on parsing and building the query plan each time the result is required. Increased efficiency is even more evident for statements that do not return a resultset (such as INSERT, UPDATE and DELETE) because the actions needed to construct a resultset are generally more timeconsuming than parsing and creating an execution plan.

Now, we need to elaborate just a little on this efficiency argument. In the Reference manual, you will bump in to this phrase pretty quickly:

So, even though the statement has been prepared, we can still enjoy the flexibility of controlling the query result.

One thing to keep in mind is that parameters are not implemented using simple string substitution. For example, the placeholder in the previous example is not quoted inside the statement. It merely provides a slot for a value, and the binding process takes care of transferring that value to the slot inside the statement. Quotes are merely syntactic methods to distinguish a string from the surrounding code. Because the binding process is way beyond the level of parsing, it does not make sense to use quotes.

You can't just put a placeholder wherever you like. Parameter placeholders can appear only in those places inside the statement where you would normally expect an expression. In particular, you cannot use parameter placeholders to parameterize identifiers or entire statement structures. So, the following attempt fails with a syntax error, because the parameter placeholder appears where you would normally put an identifier:

mysql> prepare stmt from'create table ? (id int unsigned)';ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near '? (id int unsigned)' at line 1

However, is is not impossible to paramterize identifiers using the prepared statement syntax. I'll show that in a bit. It's just that you can't do it using parameters, because parameters are just a special cases of expressions, like column references, literals, etc.

Parameter binding occurs in a positional manner.Each ? placeholder must be matched bij exactly one user variable in the USING clause, where the first placeholder matches the first user variable, the second placeholder matches the second user variable and so on.

You really must match each placeholder by exactly one user variable, or else you will encounter an error:

1210 (HY000): Incorrect arguments to EXECUTE

Dynamic SQL

For no particular reason, PREPARE accepts either a string literal, or a user-defined variable to define the statement to prepare. It would've been just as conceivable to accept just a statement, like so:

Cleaning up

There's one extra step in the process of using prepared statements that I did not yet mention. That's cleaning up (I guess that tells you something about me, right?). Once you've prepared a statement, the handle and the associated objects on the server's side will remain to exist until the client's session is over. This means that client will keep some of the server's resources occupied. Therefore, it's good practice to clean up afterwards. When you're sure you're done working with the statement, you should DEALLOCATE it:

mysql> deallocateprepare stmt;

This just tells the server to get rid of all the resources associated with the statement handle, and to forget about the statement handle as well.

Some links

Although it does not seem that the prepared statement syntax was designed for it (word is there will be true dynamic sql support in MySQL 5.1 in the form of the EXECUTE IMMEDIATE syntax), it sure does the job. If you want to read some articles on actually using this feature for a real purpose, check out these links:

I tried dynamic SQL as you show here for creating a procedure, not to run dynamically, but just to be created once dynamically. And I got the error "This command is not supported in the prepared statement protocal yet." ErrorNr 1295. Using MySQL 5.0.37. What is my problem?

Well, the problem is just as advertised by the error message: you cannot create stored procedures (yet) using the prepare syntax.

It's a pity - but that's how it is. There are ways to work around it though:

If you are adventurous, check out this article by Giuseppe Maxia. He explains how you can create stored procedures dynamically by directly inserting the procedure text into the mysql.proc table:http://datacharmer.blogspot.com/(search blog for "Higher Order MySQL")

i've done a stored procedure that modify multiple table, when i call this procedure into a trigger don't do any modification, in the same way when i do a "stand-alone" call to this procedure (es: CALL proc(2); ) it works.. it's a not implemented yet funcionality in MySQL 5.0.51a-9+lenny2 ? thanks and sorry for my bad english ;)

-- cursor stmt for using var:DECLARE cur_county_ouput CURSOR FOR select DISTINCT GROUP_CONCAT(CONCAT('f.fips_county_nm like ',Quote(CONCAT(title,'%'))) ORDER BY title ASC SEPARATOR ' OR ') as counties from table_nm where level=2 order by title ASC ;

Now concerning your problem with prepared statements: I have to disappoint you I'm afraid. Currently you cannot traverse the resultset returned by a prepared statement. However, there are ways around it:

1) Instead of preparing a SELECT statement, prepare a CREATE TEMPORARY TABLE ... AS SELECT statement, and write a regular cursor to traverse the temporary table

BTW - there is another issue with the code you posted. Sadly, this type of syntax:

EXECUTE stmt_listing USING v_county;

does not work. I know its daft, it should work, but alas it doesn't. What doe work is this:

EXECUTE stmt_listing USING @county;

So, conversely, your FETCH would look like:

FETCH cur_county_ouput INTO @county;

That said - maybe you can tell us what you are trying to achieve - I mean, both PREPAREd statments and CURSORs are pretty ugly beasts in MySQL and if you can avoid them (you almost always can) you should. So tell us what the result should be and I'll try and come up with a suggestion to avoid cursors and prepared statements, Ok?

Ok, I've changed my code up a bit and created a prepared statement using a insert/select to put into the temp table.

Sooo... Here I am still stuck.

What I need to accomplish is to be able to generate a dynamic where clause, which is working. If manually build the statement using the generated clause then I get the correct result.

Somehow when I try to create the prepared statement with the where clause as the variable param to the statement, I do not get any records. It makes me think that the value is either getting lost or there's a syntax error in the prepped statement.

It would be great if you could actually access properties of the prep statement to debug things like this.

So here's the code I'm using...

prepare stmt_counties from 'select DISTINCT GROUP_CONCAT(CONCAT(''f.fips_county_nm like '',Quote(CONCAT(title,''%''))) ORDER BY title ASC SEPARATOR '' OR '') INTO @county from rochesterhomepage_net.pmd_locations where level=2 order by title ASC' ;

Hello Roland,Your article doesn't distinguish between resultsets from select-statements and resultsets produced by prepared statements, so they should fit together?I am trying to combine a select-statement with a prepared statement like this:select * from dislike where name in(execute livesright using @name);It is not accepted by MySql 5.0.72.It is also not possible to union a select-resultset with an prepared statement-resultset.Do you know why? Is there a solution?

"Your article doesn't distinguish between resultsets from select-statements and resultsets produced by prepared statements..."

Thanks for pointing that out - it's a good point.

"...so they should fit together?"

The answer is no ;)I agree that it would be a nice feature. However, it is currently not supported by MySQL (and I don't know any construct from the SQL standard that describes this either)

"Do you know why?"

No - I don't. Many things that would be nice aren't implemented in many products.

"Is there a solution?"

Well, it depends on what you want to do.

You could perhaps leave the generation of the SQL statement to your application code, and not deal with server side dynamic sql at all. If you can, I would recommend that approach.

If you really must use server side dynamic sql, it would be pretty trivial to adjust the code that generates the statement text that you excute dynamically, and wrap the static part of the statement ('select * from dislike where name in (...)') in there.

Finally, if this is not acceptable for some reason (for example, if you want to run a cursor on dynamic sql), you could try to use dynamic SQL to generate a view, and use that view instead. In this particular case you would have to somehow generate the sql statement without a reference to the ? placeholder (so you would have to hardwiare the parameter as a constant value into the view code). You would also need to make the view name unique for your connection (use connection_id() as part of the view name) and after you are done, clean up the view.

Thank you very much for your fast answer!So it's really a pity because I asume now, that it is a matter of fact that also the combination of select-resultsets with stored routine-resultsets will not be possible as far as procedures are concerned.It works with functions but they can't give back a resultset but only a single value...Maybe this will be a new feature in MySql 6?

"So it's really a pity because I asume now, that it is a matter of fact that also the combination of select-resultsets with stored routine-resultsets will not be possible as far as procedures are concerned."

That is correct. That said, personally I don't see why you really need it to be stored procedures...why not use a view instead?

"It works with functions but they can't give back a resultset but only a single value..."

Yes, you can use function calls in SQL statements (and stored routines for that matter).

I know that postgresql and ms sql server do have facilities for this type of thing (table functions is the term for this device I believe)

Hi Roland,I want to simulate a PROLOG-Knowledgebase with MySql and thought of using stored procedures to realize the more complex rule-statements of PROLOG.But I thought about your suggestion of using views and will start now with that.

Roland pointed me to your comment, as I did a presentation at the MySQL Users Conference in Santa Clara back in 2007, titled "The Declarative Power of VIEWs". There I compared PROLOG to MySQL and implemented a simple knowledge base/expert system using MySQL VIEWs.

So maybe you might want to check that out online: It's definitely something along the same lines you want to go, though just a tiny "proof of concept"/demo.

Hi:I really like you article,it is very good and it help me with a problem but I have another one, I´m doing a store procedure and already I have it and it works but I have to give the name of a table as a parameter, at this moment I know how to use the prepared statement buy in my procedure I use a sentence with exists and not exists, then my question, Can I use the prepared stmt with exist?

The error: Script line: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @qry2=CONCAT('SELECT ', usingtable,'.anio from ', usingtable,' where ', usin' at line 21

yes, should just work. However the way you set it up will not work, because the EXISTS operator expects a query. Although you can use a prepared statement to execute a query, it is not a query itself - it is a prepared statement. Confusing perhaps, but it does make sense.

Anyway, there is another way to solve your problem. Basically, your code has this form:

IF EXISTS ..dynamic SQL...AND NOT EXISTS ..dynamic SQL...THEN ...

The trick is to execute the dynamic SQL before the IF, and test for the result inside the IF. Now, I don't have quite enough information to write the most optimal solution, but one way of doing it would be:

The idea is that a table is sorted > the handicap on the first of January is retreived and that the second hcp. is adjusted based on the first hcp., the third based on the second, etc.If this is done for one player then the loop starts again for player two, etc. If someone can give me a push in the right direction this will be very much appreciated!

First of all Thank You Very Much for a very helpful article you published. It helps a lot.

well I need your help in one of my problem. which is as follows.

I tried to execute a dynamic query in a function but not succeed and then I come to know that dynamic queries are not supported in functions.So I move my code to a procedure where it works fine & set a out variable as output.

Then I try to call this procedure in a function as any way I want to return a value but when I try this again I go to :-( the previous loop of following error...Error Code : 1336Dynamic SQL is not allowed in stored function or trigger

so is there any way to execute the dynamic queries in a function ?

Actually by doing all these what I want to achieve is as follows. So if you have any idea for how to achieve these then plz share your valuable views.

I am having a table1. DataTableid-----field1---field2---fieldn---itemid1--------5--------2--------10--------12--------8--------7--------9---------2

Now I having one meta data table where the definition of field1, filed2 etc is defined at row level.2.MetaDataTableid----- fieldName-----fieldDbColumnName1--------TAX1--------------field12--------TOTTAX1---------field1*503--------TAX2--------------field24--------Something-------field1*field2

So these are my two tables & I want actual field value from DataTable by passing a fieldname of MetaDataTableso my query would be something like...

As for your problem: you are mixing the metadata query directly with the data query. The way to go is to query the metadata first to get the information to generate the appropriate sql statement text for the data query, and then dynamically execute that. You can either use PREPARE for that, or you can solve it in your application.

That said, I think it is better to avoid these types of solutions. I mean, what is the benefit of queying your database through this self-made metadata layer? It would be much better if you could simply avoid dynamic SQL and write the appropriate query directly.

Thanks for a prompt reply. Finally I'll go for the simple query instead of dynamic. The reason why I choose the above way is that in my application I would like give the flexibility to the users to define the formulas for instance.

in UI they see Tax1, Tax2, Tot Tax1 etc... but in back end I am having field1, field2 etcand I store the formulas accordingly. So this way there is not dependency on business layer. User will define what they want and my query will give the results dynamically. But :-( unfortunately time constraint and some other pending tasks are there so I prefer to go by other way. Anyway when I success will again come to disturb you.

Thank you very much for your valuable inputs which really help me.

One more thing would like add is, "The way you answer our queries and the way you explains the things, it creates a feeling that you are standing in front of us and guiding us."

Hi Picas, I am not exactly sure what your question is. But I get the feeling there are a couple of things wrong with your code. For exampl, you are mixing user-defined variables with local variables. Perhaps you should clean up your code and ask on some forum. Be sure to alsways mention what error message you are getting.

Error Code : 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@sql_text:=concat('insert into ',@tahuntabel,'(kode_aset, no_aset) values('kodea' at line 10(0 ms taken)

hi, My name is Sumit. I have a situation where i am creatig a prepared statement as i have ti give the value to limit dynamically.The code is as follows:--

set @a=concat('select PushQueueID,mobileno,RegDateTime,SentDateTime,Comments,PushProviderID,status, ResponseMessage,KeyWord, case when MC.Source is null then PQ.Source else MC.Source end as Source, case when PQ.Message is null then MC.Message else PQ.Message end as Message, StartSchedule,EndSchedule,PQ.MessageID from PushQueueMessages PQ Left join MessageConf MC on PQ.MessageID=MC.MessageID and length(MC.message) > 1 where Status=0 and recordstatus =1 and PushProviderId=intPushProviderId and StartSchedule <=sysdate() and sysdate()< EndSchedule limit ',throughPut);

PREPARE stmt2 FROM @a;

now what i want to do is i want the whole result to be put into a temporary table name bkpTempTbl;

Hi Roland- looks like it's been a few months since any activity on this post/blog. But, I'll try anyway...

Nice article by the way. Helped me a bunch. But I still have a problem.

I'm "cloning" a web app a sister company wrote. They dropped it on top of an MS SQL Server db and my company doesn't want to drop the coins for SQL Server so we went with MySQL. The sister company wrote the app with every query in a stored procedure and there are a few that employ dynamic SQL. My boss doesn't want me to modify any of the backend server code if at all possible. So I'm kind of stuck at this point.

The query I'm trying to dynamically build is one query with 4 sub-queries and uses 3 of the parameters in all 5 queries. I set up 15 user variables and assigned them as such (just an example - not the real thing):

declare uservar1, uservar2, uservar3, queryText varchar(500);

##build the dynamic query substituting ? for the parameter names...

set queryText = 'select....';

set uservar1 = param1;set uservar2 = param1;set uservar3 = param1;

set @a = uservar1;set @b = uservar2;set @c = uservar3;

prepare stmt from @queryText;

execute stmt using @a, @b, @c;

deallocate prepare stmt;

END;

I get the error: Incorrect arguments to EXECUTE

You stated:

"You really must match each placeholder by exactly one user variable, or else you will encounter an error..."

I thought this should work but doesn't. Any idea why?

Thanks so much for your blogs. I've read several of them and they are all informative.

table_schema='an1'; declare continue handler for not found set done=1; OPEN CUR1; REPEAT FETCH CUR1 INTO COL; IF not done THEN IF concat('OLD.', COL) <> concat('NEW.' ,COL) THEN INSERT INTO auditlog VALUES('TB1', COL, concat('OLD.', COL),

concat('NEW.' ,COL), now() ); END IF; END IF; UNTIL done END REPEAT; CLOSE CUR1; END;//

there are a few problems with your code. The main problem is that you cannot dynamically refer to the OLD and NEW columns. There is a way around it though - you can write queries on the information schema that generate the trigger code that performs the task of logging to the audit log.

However, there are a few serious problems with using triggers. The main problem is that triggers are not fired for UPDATEs and DELETEs that result from cascading foreign keys. Another problem is that triggers are quite slow, so you're going to get a performance hit. The third problem is that you can have only one type (as in BEFORE/AFTER, INSERT/UPDATE/DELETE) of trigger per table, so if you're using these audit triggers, you cannot also use application triggers (or you'll have to find something clever to merge application level triggers with audit triggers. If you generate the code, you code implement some hooks in there to always call a stored procedure but still, it's not a very elegant solution.

I would advise you to try it another way. You simply need an audit trail, right? Couldn't you use the binary log? If you use row-based logging, and then use the mysqlbinlog tool (in combination with --verbose) you can see exactly what is going on. You just need something that periodically analyzes the binlogs.

I'm working with a SP and the SP has to take a "list" (could be 1, could be 10) of IDs. Part of the select statement is "select * from table where id in('id1', 'id2', 'id3') and ...". Now, that works perfectly if the application generates the query string and submits it to the DB. But the SP returns nothing. The app stores the IDs in a variable like this:

parm1 = "'id1', 'id2', 'id3'";

Again, if the app generates the query it works but if that same string is passed to a call to the SP, I get nada.

I coded the SP to generate a dynamic query as well as a static one. I cannot get it to work either way.

if you're using a plain, "orphan" SELECT statement (in order to return the resultset to the client), it should work fine with the dynamic SQL statement. Most likely, there is some error in the generated SQL. Just use

SELECT @generated_sql;

to find out if there is some logical error in it. If you like, you can post it here, and I can try to have a look at it.

PS: I noticed you are quoting the values. Is that correct? I mean, typically ID is a surrrogate integer key, so I'd expect a list of integers like:

Yeah, if the ID was simply an integer it would make this whole deal easier. But, the ID field in this case is a char field and the values can be alpha, numeric or alphanumeric. So, got to have them quotes.

I did look at the generated SQL using select @queryText. Didn't look like there were any errors.

I'm going to go with the app generated select statement for now as it works.

Now if I can figure out how to get the slow, slow query to execute faster. I think it's the in(). Am I correct in my thought that if columns are passed through a function like in() in a select, that MySQL cannot use an index then? When the user selects just one ID, the query returns in about 2 or 3 seconds. When the user selects 2 or more IDs, the query takes 3 to 5 minutes...

ok sir!... actually I have an prepared statements here or rather parameterized query... but i have a problem with regards to displaying many records... In addition to this I have a problem regarding bind_param;

the sample code you posted is PHP. This is completely outside the scope of this blog post.

Apart from that, you mention that you have a problem, but fail to give even the slightest clue as to exactly what problem you are experiencing. Your code doesn't help either - it won't even run as it contains PHP syntax errors.

Please, put some effort into properly researching what problem you're trying to solve. And please, post your questions on a forum, or google it.

Mr Parker, this is a very interesting question...If only there would be a website that would allow people to search the internet, or a site that would allow people to post question, so that other, more knowledgeable people could answer them...

Hi Roland,I m using prepared statement in one of the my stored procedure where i derive table name from some input parameters.i am afraid if the global variables used in my query will give me proper results in mysql cluster's multi threaded environment.

is it possible that after execution of statementEXECUTE stmt; say it fetched 76 in @authid but before execution of SET l_authId = @authId;some other thread may change the value of @authid to say 89 and i get 89 in l_authid instead of expected value 76,because of mysql's multithreaded env and as @authid is a global variable.i hope u may get some understanding of my doubt by this.basically is it possible for some other thread to access @authid and change value in between execution of EXECUTE and SET statements.do i need to use some locking before and after these two lines?also if everything is ok than i also want to know about performance of above code as my application will require very high scalability.

please note that this is not a free forum. Go to stackoverflow next time.

Short answer: no, that is not possible. @auhid is a user-defined variable. User-defined variables have session scope, not global scope. So if it contains a value you didn't expect, I'm afraid your procedure is wrong somewhere.

is it possible the last time you called the prepared statement the derived table didn't yield any rows, and thus did not write a new value to the user-defined variable? In that case, it retains whatever was in it from the previous call.

Thanks Roland, i put this procedure in a trigger e now i have another problem."Dynamic sql is not allowed in a stored function or trigger".But i use the dynamic sql in a procedure and not in a trigger!!I must find another way or there is a little hope??

it always goes like this. People post a comment that is only slightly on topic, and I try to gently point them to the manual. They come back, I help em one step, and they post again: "now i have other problem".

Please. This is not a forum. This is not google. This is not stackoverflow. Don't abuse the comments section for these things.

Hi Roland,I have just started studying mysql few months ago I want to create a trigger which sends automatic emails on updation of any field in database i wrote a trigger delimiter //create trigger email_senderafter update on loan for each rowbeginset @to="myfriend@haha.com";set @from="saumitra@gmail.com";set @subjct="hello";set @body="body";set @update=100;set @email_path=concat("'",'c:\\',now()+0,'.eml' ,"'");set @myvar=concat('select ','\n',"To:",@to,'\n',"From:",@from,'\n',"Subject",@subjct,'\n',"Body:",@body,'\n',@update,'\n',' into outfile ', @email_path);PREPARE sender FROM @myvar;execute sender ;DEALLOCATE PREPARE sender;end; //delimiter ;

but it is showing an error ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

like the error message indicates, dynamic SQL inside triggers is not allowed. Last time i played with this kind of stuff, i think you could get around it by putting the dynamic sql inside a procedure and calling that from the trigger. Otherwise, you're out of luck I'm afraid.

Can't you just poll for changes from the application layer, and send mail from there?

SET @Command1 = CONCAT("insert into TABLE16" , ...etc.. );PREPARE stmt1 FROM @Command1 ;EXECUTE stmt1;DEALLOCATE PREPARE stmt1;-------------------------------------But when I execute the stored procedure, the INSERT works intermittently. sometimes all the 16 inserts works, but sometimes it doesn't. In the last CALL of the stored procedure, the first 2 inserts (into TABLE1 and TABLE2 ) and the last 4 inserts (TABLE 13 to 16) work, but not the inserts into Table 3 to 12.Can you explain why ? Can't be because I'm using the same variable/handle command1 and stmt1 ?Paul

Took me time to read all the comments, but I really enjoyed the write-up. It proved to become Pretty useful to me and I am positive to all the commenters here Its always great when you can not only be informed, but also entertained Im certain you had fun writing this write-up.

First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures.

My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:

Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.

First off great blog post. I am running into a similar problem to what Flopsy posted a couple years back where I am trying to run a PREPARE statement inside a CURSOR. You noted that there is almost always a way to achieve this without a PREPARE or a CURSOR; I wanted to get your feedback as a possible way to eliminate these pesky structures.

My specific situation is a shopping cart situation where I am trying to determine if a discount is applicable. The is_applicable column contains a COUNT() SQL statement; if the COUNT is greater than 0, then the discount would apply.. As a site may contain multiple discounts, I had anticipated using a cursor to iterate through each of the site's rows and then evaluate the row to determine if the discount is applicable. The (simplified) table in question is:

Any input on ways to get around this limitation are valued. If I can't get away from either the cursor or the dynamic sql, I will be headed down one of the other options you outlined in a response to Flopsy, either TEMPORARY TABLE option or VIEW option.

in this case, it depends completely on what statements you stored in the is_applicable column.

What I would try to do is investigate the statements that are in there now, and try to categorize them. My assumption is that the different statements will fall into a fairly small set of types of statements.

Then, I'd try to analyze those statement types to see which data items (tables, columns) are used to determine if the discount is applicable.

In a further step, you'd have to analyze in what way these data items are related to the site rows. If you find that there are now relationships, then you will need to extend your database schema to store the relationships between that data and the sites to which they apply. An implicit data item of this sort is the data that determines why you assigned any given statement to a particular site.

When you did all that you should be in a position to write a single query that can determine the discounts. By then you won't need a cursor anymore, nor would you be fored to use dynamic SQL.

Thanks for your reply and sorry for my delay in getting back to the blog. Between the holidays and work, there has just been no free time!

I believe your analysis is 100% correct in that there will likely be a small set of statements or columns that are needed to do determine if a discount is applicable. Unfortunately, as I am building the application, I only have one client and limited business knowledge and thus I am trying to build a solution that is as flexible as possible.

That being said, I was able to build a proof of concept and wanted to post it here to get your feedback as well as provide a possible solution for others who are in my predicament.

I have made some adjustments to the table and stored procedures previously posted, so I will repost them for clarity.

Adding some data to represent discount to be applied to a fictional invoices table:INSERT INTO discounts (discount_calc) VALUES ('SELECT 5 FROM invoices WHERE id = 1'),('SELECT 10 FROM invoices WHERE id = 2');Note: Where clause is arbitrary and can be as complex as one desires.

Now for the good stuff! I have two stored procedures the first is a wrapper procedure and could possibly be eliminated for this example, however, I am leaving it as is it helps illustrate how the cursor is segregated from the worker procedure.

In the worker procedure, I use the MySql CONCAT function to build a string which sets a discount variable. If this returns with a value, then we update the invoices table with the applicable discount.CREATE PROCEDURE spDiscountsIterate(IN inDiscountId INT, IN inInvoiceId INT) BEGIN SET @colsql = CONCAT('SET @discount = (', (SELECT discount_calc FROM discounts WHERE id = inDiscountId LIMIT 0, 1), ')');

PREPARE stmt FROM @colsql; EXECUTE stmt;

IF @discount <> 0 AND @discount IS NOT NULL THEN UPDATE invoices SET discount = @discount WHERE id = inInvoiceId; END IF;

I am actually making procedure where I need to prepare the 'execute stmt using @lv_constant_1,@lv_constant_2' statement. This is because the dynamic variables are being generated in the procedure itself.

It says 'This command is not supported in the prepared statement protocol yet'.

Can you please help me out. I have created prepare statement in procedure. But when I execute procedure then it give me an error."#1243 - Unknown prepared statement handler (auto_partition) given to EXECUTE"

Good Morning Roland, Very nice article and many good feed backs. Thanks for serving the society.I am struck with a procedure converting columns data to row data. So I build a procedure to read each loop the new column name. Prepare statement is not identifying the variables. The code is given below, I appreciate your help...........DECLARE CONTINUE HANDLER FOR NOT FOUNDSET no_more_accounts = 1;set vx = 1;

Hi Roland,The error I am getting is produced below: My problem is the fetched or userdefined column names are not recognised while preparing statement. Kindly look into it.Thanks and regards .. varughese

1) There is no closing parenthesis: )2) Those strings are not quoted, thus they will be interpreted as field names. I don't see what table they would be getting pulled from as there are no other tables mentioned besides the one you are inserting into.