CODEWITHSUNDEEP
mysqlprocedure
Mr. A
Mr. A

Reputation: 103

procedure returns null value

I have written a simple demo procedure below is the code

delimiter //
create procedure test2(IN boss int, out name varchar(50))
begin
select Name into name  from employee where Reports_to=boss;
end //

when I call this procedure as CALL test2(2,@name) it returns null

though the table has all the values -

mysql> select * from employee;
+--------+--------+--------+-------------+------------+
| Emp_Id | Name   | Salary | Department  | Reports_to |
+--------+--------+--------+-------------+------------+
|      1 | Alok   |  50000 | RnD         |       NULL |
|      2 | Mahesh |  40000 | Development |          1 |
|      3 | Ramesh |  40000 | Development |          1 |
|      4 | Bimal  |  30000 | Sales       |          2 |
|      5 | Javed  |  20000 | Marketing   |          4 |
|      6 | Mukesh |  35000 | Accounts    |          3 |
+--------+--------+--------+-------------+------------+

and also If I write CALL test2(1,@name) it shows error

result contains more than one row

how to solve these two errors...??

Upvotes: 1

Views: 53

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30849

The below query fails as it can't assign more than one value to a variable:

select Name into name  from employee where Reports_to=boss;

What you want is something like GROUP_CONCAT which would return comma separated values. Try the following:

SELECT GROUP_CONCAT(name) INTO names
FROM employee
WHERE reports_to = boss;

Upvotes: 2

Related Questions