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
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;
- In stored procedure you must declare variables using the declare keyword.
- Variables declared with DECLARE keywor does not take @ sign. They are simply identifiers.
- You can set them using Set = [new value]. In some editors, use set := [new value]
- Declare statement must come first after BEGIN keyword in Stored Procedure. All variables must be declared first.
- 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();