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.