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?
You can use SET command for temporary variable assignment. Display all records from the table using select statement. Display the value of variable @findAge.
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.
Description. := Assign a value. = Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
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.
try this one,
SET @Age = (SELECT Age FROM People WHERE PeopleId = 16);
or
SELECT Age INTO @Age
FROM People
WHERE PeopleId = 16;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With