David King

full stack developer

All Blog Articles

Prev « TDD - I need help! Next » A Workout for Productivity


MySQL Procedures and Parameters

26 Sep 2013

When it comes to databases, I've tried to keep myself in the know about all the tools, features and best-practices even if I never utilise them. It's useful to be aware of the things you have yet to learn so that should you ever come accross a new problem your ability to think outside of the box is improved by an incling of an idea.

Anyhow, this week I've finally needed to use MySQL PROCEDURES and TRIGGERS to help compliment some of the more complex queries we need to run (the details of which are beyond the scope of this wee blog post). And I quickly came about an issue regarding PARAMETERS that has had me scratching my head for some time.

The evolution of a PROCEDURE

Lets start with a simple SQL query, you want to increment a column for a specific row:

	UPDATE `mytable`
		SET `mycolumn` = `mycolumn` + 1
		WHERE `id` = 1234;

Then you want to make your query more dynamic by utilising User-Defined Variables:

...maybe you are using prepared statements to similar effect...

	SET @myid = 1234;
	UPDATE `mytable`
		SET `mycolumn` = `mycolumn` + 1
		WHERE `id` = @myid;

Finally you decide create a PROCEDURE that you can call like a function:

	DELIMITER $$
		DROP PROCEDURE IF EXISTS `MyProcedure` $$
		CREATE PROCEDURE MyProcedure(IN myid INT)
		BEGIN
			UPDATE `mytable`
				SET `mycolumn` = `mycolumn` + 1
				WHERE `id` = myid;
		END $$
	DELIMITER ;

That procedure is then stored within the database and can be used by any connection later:

	CALL MyProcedure(1234);

Works a treat. But to get to that functional PROCEDURE I took a massive, confusing detour...

Where I went wrong

If you look at the 3 statements above you will notice:

  1. The first uses an explicit 1234 within the statement
  2. The second uses the @myid User-Defined Variable
  3. The third uses a myid Parameter variable
    • Note the lack of @-sign

The following PROCEDURE uses BOTH @variables and paramenters:

	DELIMITER $$
		DROP PROCEDURE IF EXISTS `MyProcedure` $$
		CREATE PROCEDURE MyProcedure(IN myvar VARCHAR(255))
		BEGIN
			SELECT myvar, @myvar ;
		END $$
	DELIMITER ;
	-- SET @myvar then call the procedure
	SET @myvar = 'variable';
	CALL MyProcedure('parameter');

	-- +----------+-----------+
	-- | myvar    | @myvar    |
	-- +----------+-----------+
	-- | variable | parameter |
	-- +----------+-----------+

You can see that the PROCEDURE has access to both the User-Defined variables AND the parameters. Very confusing.

Lessons learned? MySQL PROCEDURES have access to Session scope. I don't know why, but I wouldn't have expected that.


Prev « TDD - I need help! Next » A Workout for Productivity