Getting ERROR 1064 (42000) while declaring a variable in mysql 5.7

Question

Im trying to declare a variable i mysql but strangely none of these commands are working. Ive snipped these commands from stackoverflow solved questions and while these seem to work for most of the people , its throwing same error on my system.

MySQL Server version: 5.7.26-0ubuntu0.18.10.1 (Ubuntu)
> declare  @d1  decimal(10,2)
> declare  d1   float(10);
> declare `d1`  BIGINT(2);
> declare  d1   INT;

All these are giving same error

 ERROR 1064 (42000): You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax
 to use

Also, this command print nothing for me, no error just blanks :-

 -> DECLARE @COURSE_ID INT
    -> SELECT @COURSE_ID = 5
    -> PRINT @COURSE_ID
    -> 
    -> ;
    -> 
    ->

This might help. [https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference] — SScotti, Jun 30 '19 at 05:23
You can only declare in stored programs (procedures,functions,events,triggers) — P.Salmon, Jun 30 '19 at 06:01

Kartik Gautam · Answer 1 · 2019-07-01T12:30:10.023

0

User-defined variables can be accessed without declaring or initializing. Referring to a variable not been initialized will have a value of NULL and a type of string.

Using SET statement:

SET @X=1 ;

Could be accessed by the simple select statement:

SELECT @X; /*will result in 1*/
SET @X=@X+2; /*will update X to 3 now*/
SELECT @x; /*will result in 3*/

Being Session-specific, user variables can assign values from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

For Local variables: They needs to be declared using DECLARE before being accessed and can be used as local variables and the input parameters inside a stored procedure

DELIMITER //
CREATE PROCEDURE Vnita(n int)
BEGIN
DECLARE X INT DEFAULT 1;
END
//

Don't forget to change delimiter back to semicolon...

DELIMITER ;

--Thanks for asking.

edited Jul 01 '19 at 12:30

answered Jun 30 '19 at 07:40

Kartik Gautam

108
7

Ok but then how do I print the values of X? None of this is giving any output --> select (@X); print (@X); – vinita Jun 30 '19 at 09:30
Pls check question last 10 lines, updated additional info – vinita Jun 30 '19 at 09:49
User-defined variables are accessible by SELECT @X; after you have set them...please check that you reset your DELIMETER BACK TO SEMICOLON. On the other hand variables decalred in procedures are local variables and will not be accessible outside procedure, selecting them results in NULL, as they are considered yet not set. – Kartik Gautam Jul 01 '19 at 12:24

Getting ERROR 1064 (42000) while declaring a variable in mysql 5.7

1 Answers1