MySQL Stored Procedures

Here is a tip you can use when writing a stored procedure, and using prepared statements.

Since you have to pass the statements in through EXECUTE stmt USING ...   it can get confusing if there are a large number of parameters.

I had this happen recently when I had about 40 parameters in one query.

Although you can pass the same parameter multiple times,  like this:

I did something that would allow me to easily match up the parameters with the lines they go with in the stored procedure.

Here’s a snippet as an example:

I copied the code to another editor, and added line numbers. I could have left them in the stored procedure but chose not to in order to keep it clean.

Then, I was able to make sure that the variables were in the right places by naming them the same way:

Also, notice how I group them together, leaving a space at each place where there is a new line.

You could do the spacing and grouping trick and not use line numbers too, since you can reuse variables in the list, but I prefer this because it is very explicit and clear.