How to write DRY SQL in MySQL – Part 2: Stored Procedure Hacks

In Part 1 we looked at using VIEWS to keep our SQL DRY. Unfortunately, there are cases when VIEWS are either not allowed or perform terribly and can’t be optimised.  Stored Procedures are, of course, the mainstay of writing DRY SQL, and optimisation.  Unfortunately, you can’t treat the result set of Stored Procedure as a query; therefore you can’t join stored proc results in queries and so developers often end up having to copy their SQL leading to WET code. However, there’s a neat little hack which effectively lets you treats stored procedures like views.  It’s a little more work, but it’s worth it.

There are some very common queries that MySQL doesn’t support as VIEWs:

1. Any query that contains a subquery in the WHERE clause. E.g. where myfoo not in (select foo from bar). Fortunately, you can usually rewrite these using a left join and then filter any rows with nulls on the right hand side.

2. VIEWs that contain group/count aggregate functions cannot be made to use ALGORITHM=MERGE.  THIS IS BAD, especially if you naively forgot to set the algorithm, or just didn’t know about them: MySQL will happily build the view using ALGORITHM=TEMPTABLE instead of merging it with your query at runtime. If you have a generic query that covers a large result set, you won’t be able to use a view without bringing down the whole system!  You might not even notice this is happening if the project is new and as your data grows it’ll come back and bite you in the ass.

It’s pretty likely anyway that you will want to encapsulate, or already have encapsulated, complex queries in parameterised stored procs, so making them work like normal queries is incredibly useful and keeps your SQL DRY.

In order to make sprocs joinable, we can take advantage of temporary session tables.

Temporary tables stick around as long as the session is active, unless you explicitly drop them. So, instead of returning the result set from a stored procedure, we just put the results into a temporary session table, and whatever calls the sproc will have access to that table.  We can parameterise the sproc to keep the set small, and we can have queries as complex and procedural as we want!

This little hack will even work with your middleware code so long as it’s capable of running complex SQL (i.e. more than a single statement).  This is almost always the case.

Here’s an example:

DROP PROCEDURE IF EXISTS sp_create_tmp_myquery;
CREATE PROCEDURE sp_create_tmp_myquery(  )
BEGIN
-- this sproc creats a session temp table for use OUTSIDE the procedure 
-- essentially allowing DRY reuse of the query 
-- if the query or sproc calling this doesn't drop the temp table it'll be dropped in the next call 
-- to this sproc or when the session closes
  
  drop temporary table if exists tmp_myquery; 

  create temporary table tmp_myquery
  as
  -- some complex sql. Knock yourself out.

END;

-- to use the sproc:
call sp_create_tmp_myquery();

-- work with the results 
select foo
from   bar
join  tmp_myquery on <some condition>;

-- or just select them
select * from myquery
-- drop the table explicitly; not necessary but cleaner
drop temporary table if exists tmp_myquery; 

You’re not limited to a single data set either. If you want or need to, you could create multiple tables in the sproc and work with them after.