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:

EXECUTE stmt USING @p1,@p2,@p1,@p2,@p3,@p4

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:

AND
  CASE
   WHEN ? = 1 AND ? = 1 AND ? = 1 THEN #l2
   last_seen BETWEEN ? AND ?  #l3
    WHEN ? = 1 AND ? = 1 THEN #l4
     (last_opened BETWEEN ? AND ? OR last_viewed BETWEEN ? AND ? ) #l5
    WHEN ? = 1 AND ? = 1 THEN  #l6
     (last_opened BETWEEN ? AND ? OR last_clicked BETWEEN ? AND ? ) #l7
    WHEN ? = 1 THEN #l8
     last_opened BETWEEN ? AND ?  #l9
    WHEN ? = 1 AND ? = 1 THEN  #l10
     (last_viewed BETWEEN ? AND ? OR last_clicked BETWEEN ? AND ? )  #l11
    WHEN ? = 1 THEN #l12
     last_viewed BETWEEN ? AND ?  #l13
    WHEN ? = 1 THEN #l14
     last_clicked BETWEEN ? AND ?  #l15
    ELSE
     last_seen BETWEEN ? AND ?   #l16
    END

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:

USING ..., @l2use_opens,@l2use_views,@l2use_clicks, @l3start,@l3end, @l4use_opens,@l4use_views, @l5start1,@l5end1,@l5start2,@l5end2, @l6use_opens,@l6use_clicks, @l7start1,@l7end1,@l7start2,@l7end2, @l8use_opens, @l9start,@l9end, @l10use_views,@l10use_clicks, @l11start1,@l11end2,@l11start2,@l11end2, @l12use_views, @l13start,@l13end, @l14use_clicks, @l15start1,@l15end1, @l16start2,@l16end2, ...;

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.