ThatMoose ThatMoose - 1 month ago 6
MySQL Question

How do i create a stored procedure that accepts a user Id and returns the user’s full name and occupation and 1 email address

delimiter $$

create procedure userInformation(in uID varchar(50), out fullname varchar(50), out job varchar(50), out email varchar(50))
begin
select concat(firstname, ' ', lastname) into FULLNAME
from users
where userid = uID;

select occupation into JOB
from occupation
join users on users.occupationID = occupation.occupationid
where userid = uID;

select useremail into EMAIL from useremail
join users on useremail.userid = users.userid
where userid = uID
limit 1;

END$$

delimiter ;
call userInformation('00002380-3e3b-11e3-bbac-c42c03098f6c', @fullnam, @job, @email);
select @fullname, @job, @email;


I get this error when I try and call/select the procedure: Column 'userid' in where clause is ambiguous

Answer

When writing code in MySQL, here are three rules:

  • Always qualify column names in queries.
  • Always distinguish parameter names by naming conventions.
  • Always distinguish local variables by naming conventions.

So, I would write the code more like this:

create procedure userInformation (
    in in_uID varchar(50),
    out out_fullname varchar(50),
    out out_job varchar(50),
    out out_email varchar(50)
   )
begin
      select concat(u.firstname, ' ', u.lastname)
      into out_fullname
      from users u
      where u.userid = in_uID;

      select o.occupation
      into out_job
      from occupation o join
           users u 
           on u.occupationID = o.occupationid
      where o.userid = in_uID;

      select ue.useremail
      into out_email
      from useremail ue JOIN
           users u
           on ue.userid = u.userid
      where ue.userid = in_uID
      limit 1;
end$$