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.

Rules

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.

Setup

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;

Stored Procedure

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 ; 

Usage

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;