MySQL:- Variable – User-Defined Variables and Local Variables

Variables

User-Defined Variables

To declare user-defined variables, please prefix your variable name with @.

User-defined variables are not explicitly defined.

Please initialize variable by using set ( set @name=’julia’ ) or issue ( select @student = ‘June Baker’ ).

 

Sample Code


set @number1 = 10;
set @number2 = 15;
set @answer = @number1 + @number2;

select 
          @number1 as 'number1'
        , @number2 as 'number2'
        , @answer  as 'answer'
        ;


 

Local Variables

Whereas, Local Variables can only be used in stored objects such as Stored Procedures and Functions.

To declare variables, please use the syntax below:-


declare @tsCreated datetime;

Sample Code


DROP FUNCTION IF EXISTS funcCalcAdd;
    
DELIMITER //

CREATE FUNCTION funcCalcAdd ( number1 INT, number2 INT )

RETURNS INT

DETERMINISTIC

BEGIN

    /* Declare Local Variables */
    DECLARE result INT;

    SET result = 0;

    set result = number1 + number2;
   
    RETURN result;

END; 
//

DELIMITER ;        

 

Error Messages

Local Variables

SQL Error [1064] [42000]: You have an error in your SQL syntax

If one tries to declare a local variable outside of a stored routine ( stored procedure or function ), by issuing:-

declare @tsCreated datetime;

One will get an error that reads:-

Image

Textual


Error occurred during SQL script execution

Reason:
SQL 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 near 'declare number1 int' at line 15

 

SQL Error [1193] [HY000]: Unknown system variable ‘number1’

If one tries to use a local variable outside of a stored routine ( stored procedure or function ), by issuing:-

set number1 = 10;

One will get an error that reads:-

Image

Textual

Error occurred during SQL script execution

Reason:
SQL Error [1193] [HY000]: Unknown system variable 'number1'

Example

Due to the absence of the ampersand ( @ ) and as we are not in a stored routine ( Stored Procedure, function ), the system is not treating the variable as a local variable.

 

Summary

There is not much in this post.

For me, trying to learn MySQL, there is a bit of headache.

One gets some code from an online post and quickly tries to use it, but it fails and you find yourself staring at the screen.

Please continue to try to separate out what works in which context ( session or stored program ).

Leave a Reply

Please log in using one of these methods to post your comment:

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