Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL temporary variable assignment

I have a table like the one below.

CREATE TABLE People(PeopleId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
                    Name VARCHAR(255), 
                    Age INT);

INSERT INTO People(Name,   Age)
            VALUES('Sam',  25),
                  ('John', 24),
                  ('Ria',  14),
                  ('Diya', 23),
                  ('Topel',19),
                  ('Mac',  45);

I have created a procedure where I use temporary variable age for some purpose.

DROP PROCEDURE IF EXISTS Sample;
CREATE PROCEDURE Sample()
BEGIN
  SELECT @Age = Age
    FROM People
   WHERE PeopleId = 4;

   SELECT * 
     FROM People;
END;

Don't ask why I am storing age in temporary variable since the above is not the exact procedure.

When I run the procedure, the temporary variable is getting displayed as one of result set along with the result set I get for select query. How can I avoid displaying the temporary variable as part of the result set after assignment?

like image 774
ArrayOutOfBound Avatar asked Nov 08 '12 05:11

ArrayOutOfBound


People also ask

How do I declare a temporary variable in MySQL?

You can use SET command for temporary variable assignment. Display all records from the table using select statement. Display the value of variable @findAge.

How do I assign a variable in MySQL?

MySQL variable assignment There are two ways to assign a value to a user-defined variable. You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter. The second way to assign a value to a variable is to use the SELECT statement.

What does := mean in MySQL?

Description. := Assign a value. = Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)

How do you set a temp variable in SQL?

Temp Variables are created using a “DECLARE” statement and are assigned values using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a specific batch of query execution.


1 Answers

try this one,

SET @Age = (SELECT Age FROM People WHERE PeopleId = 16);

or

SELECT Age INTO @Age
FROM People
WHERE PeopleId = 16;
like image 175
John Woo Avatar answered Sep 29 '22 10:09

John Woo