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;