Phpmyadmin declare variable in stored procedure

---

This MySQL tutorial explains how to declare variables in MySQL with syntax and examples.

What is a variable in MySQL?

In MySQL, a variable allows a programmer to store data temporarily during the execution of code.

Syntax

The syntax to declare a variable in MySQL is:

DECLARE variable_name datatype [ DEFAULT initial_value ]

Parameters or Arguments

variable_nameThe name to assign to the variable.datatypeThe datatype to assign to the variable.DEFAULT initial_valueOptional. It is the value initially assigned to the variable when it is declared. If an initial_value is not specified, the variable is assigned a value of NULL.

Example - Declaring a variable

Below is an example of how to declare a variable in MySQL called vSite.

DECLARE vSite VARCHAR(40);

This example would declare a variable called vSite as a VARCHAR(40) data type.

You can then later set or change the value of the vSite variable, as follows:

SET vSite = 'TechOnTheNet.com';

This SET statement would set the vSite variable to a value of 'TechOnTheNet.com'.

Example - Declaring a variable with an initial value (not a constant)

Below is an example of how to declare a variable in MySQL and give it an initial value. This is different from a constant in that the variable's value can be changed later.

DECLARE vSite VARCHAR(40) DEFAULT 'TechOnTheNet.com';

This would declare a variable called vSite as a VARCHAR(40) data type and assign an initial value of 'TechOnTheNet.com'.

You could later change the value of the vSite variable, as follows:

SET vSite = 'CheckYourMath.com';

This SET statement would change the vSite variable from a value of 'TechOnTheNet.com' to a value of 'CheckYourMath.com'.

13.6.4 Variables in Stored Programs

System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context. In addition, stored programs can use DECLARE to define local variables, and stored routines (procedures and functions) can be declared to take parameters that communicate values between the routine and its caller.

• To declare local variables, use the DECLARE statement, as described in Section 13.6.4.1, “Local Variable DECLARE Statement”.

• Variables can be set directly with the SET statement. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.

• Results from queries can be retrieved into local variables using SELECT ... INTO var_list or by opening a cursor and using FETCH ... INTO var_list. See Section 13.2.10.1, “SELECT ... INTO Statement”, and Section 13.6.6, “Cursors”.

For information about the scope of local variables and how MySQL resolves ambiguous names, see Section 13.6.4.2, “Local Variable Scope and Resolution”.

It is not permitted to assign the value DEFAULT to stored procedure or function parameters or stored program local variables (for example with a SET var_name = DEFAULT statement). In MySQL 8.0, this results in a syntax error.

Cant declare variable in stored procedure phpmyadmin | mysql

Questions : Cant declare variable in stored procedure phpmyadmin | mysql

2022-09-25T22:23:58+00:00 2022-09-25T22:23:58+00:00

997

I wanted to create a stored procedure that anycodings_phpmyadmin has a variable in it, so I made this

CREATE PROCEDURE SPTest()
BEGIN
    DECLARE @var INT;
END

But it gave me an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@var INT' at line 3

I've tried all the answer from anycodings_phpmyadmin stackoverflow, I'm using phpmyadmin version anycodings_phpmyadmin 4.5.4.1 and it says up to date. How can I anycodings_phpmyadmin solve this? Thanks in advance

Total Answers 3

31

Answers 1 : of Cant declare variable in stored procedure phpmyadmin | mysql

use delimiter:

delimiter 
CREATE PROCEDURE SPTest()
BEGIN
    DECLARE @var INT;
END
| 
delimiter ;

0

2022-09-25T22:23:58+00:00 2022-09-25T22:23:58+00:00Answer Link

mRahman

2

Answers 2 : of Cant declare variable in stored procedure phpmyadmin | mysql

I believe your problem is twofold; the anycodings_phpmyadmin use of @ in the variable name doesn't anycodings_phpmyadmin seem to be supported by MySQL, which is anycodings_phpmyadmin compounded by an apparent phpMyAdmin bug anycodings_phpmyadmin which is causing phpMyAdmin to fail even anycodings_phpmyadmin if the SQL is valid.

I had luck at the command line using anycodings_phpmyadmin this statement:

DELIMITER &
CREATE PROCEDURE SPTest()
BEGIN
    DECLARE v INT;
END
&
DELIMITER ;

0

2022-09-25T22:23:58+00:00 2022-09-25T22:23:58+00:00Answer Link

miraj

4

Answers 3 : of Cant declare variable in stored procedure phpmyadmin | mysql

If you are using Routine just add BEGIN anycodings_phpmyadmin and END

BEGIN
  DECLARE v INT DEFAULT 1;
END

i hope it will work.

0

2022-09-25T22:23:58+00:00 2022-09-25T22:23:58+00:00Answer Link

jidam

Can we declare variables in Stored Procedure?

How declare variable in MySQL procedure?

How do I execute a Stored Procedure in PHPMyAdmin?

How do I view Stored Procedures in PHPMyAdmin?