Fizz Buzz in MySql Using a Stored Procedure and a Temporary Table
As a fun mental exercise I whipped this up. Is this really useful? Probably not. It is really a “code kata” if nothing else. I wouldn’t recommend using stored procedures in most situations since it is generally considered good practice to keep your logic in your code and not in the database layer.
The standard rules for Fizz Buzz are:
- Write a program that prints the numbers from 1 to 100.
- But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”.
- For numbers which are multiples of both three and five print “FizzBuzz”.
NOTE: this program will only work for the requirements above. If you have need for unsigned ints or some other requirement, this solution will need to be modified.
Here we create a temporary table so that we can store the output:
drop table if exists `results`; create temporary table `results` ( `id` int unsigned not null auto_increment primary key, `value` varchar(255) not null ) engine=memory;
Then we create the stored procedure so that we can invoke it wherever we want.
-- We blow away the stored procedure if it is already there drop procedure if exists fizz_buzz; delimiter # -- we create the stored procedure create procedure fizz_buzz(`start` int unsigned, `max` int unsigned) begin declare `counter` int unsigned default `start`; declare `value` varchar(255) default null; -- don't want anything left in the table though above we are removing the table and then creating it truncate table `results`; while `counter` < `max` do -- here we do all the normal logic for fizz buzz using % (modulo) IF ((`counter` % 5) = 0 && (`counter` % 3) = 0) THEN SET `value` = 'FIZZ BUZZ'; ELSEIF (`counter` % 5) = 0 THEN SET `value` = 'BUZZ'; ELSEIF (`counter` % 3) = 0 THEN SET `value` = 'FIZZ'; ELSE SET `value` = `counter`; END IF; -- then we insert into the results table insert into `results` (`value`) values (`value`); -- then update the counter set `counter` = (`counter` + 1); end while; end # delimiter ;
Now all we have to do is call the
fizz_buzz() stored procedure and select the results from the table
call fizz_buzz(1, 100); select * from results order by id;