MySQL – Working with Variables

Variables in MySQL work quite different than how they work in SQL Server. I stumbled upon quite a few things when working with Variables in MySQL. Here is a short overview of it

Declare Variables

No need to declare variables using DECLARE statement. Simply use Set followed by @variable_name. You can set the variable in the same line.

set @test = 2;

select @test

Declare Variables inside Stored Procedure

If you want to declare variables inside stored procedure, the syntax is different. It is more like what you would do in SQL Server

DECLARE test int default 0;
set test=2;
SELECT test;

Caveats:

  1. In stored procedure you must declare variables using the declare keyword.
  2. Variables declared with DECLARE keywor does not take @ sign. They are simply identifiers.
  3. You can set them using Set = [new value]. In some editors, use set := [new value]
  4. Declare statement must come first after BEGIN keyword in Stored Procedure. All variables must be declared first.
  5. If you are creating stored procedure in PHPMYADMIN default editor (using routines), the syntax might give you error. Instead create stored procedure using command line.

Example Stored Procedure that uses variables

-- 1. Create stored procedure
DELIMITER //
CREATE PROCEDURE Test()
BEGIN
declare test int;
set test=2;
SELECT test;
END //
DELIMITER ;

-- 2. call the stored procedure
call Test();

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s