Run the following table definition to create a table called tests in the test database.
DROP TABLE IF EXISTS test.tests; CREATE TABLE test.tests ( id int(10) unsigned NOT NULL auto_increment, var varchar(45) NOT NULL, PRIMARY KEY (id) );
Fill it with some default values:
INSERT INTO tests(var) VALUES('one'), ('two'), ('three'), ('four');
Now lets say that you want to find the values in the table, but that you want to increment a value for each row, the following procedure will do this. If you are using the MySQL Query browser then open a script table and past the following and execute, changing any database/table definitions.
DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`Increment` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Increment`( number CHAR(64) ) BEGIN SET @total=0; SELECT id, @total:[email protected]+number AS RunningSum FROM tests; END $$ DELIMITER ;
You will now be able to call the procedure in the following way:
The value that you pass to the procedure is used to work out the increment. So in this case the output would be something like the following:
id RunningSum 1 4 2 8 3 12 4 16