1

I am trying to create a simple procedure that looks for a fname and lname when given a number

create procedure _lookup_name (_no int)
select (fname || ' ' || lname)  from table where number = _no;
end procedure;

I keep getting an error saying that "INTO TEMP table required for SELECT statement.

user867621
  • 1,147
  • 4
  • 16
  • 34

2 Answers2

1

You need to put the results of the select statement into a variable or table. See: w3schools (into table) or here temp table in memory

kentcdodds
  • 27,113
  • 32
  • 108
  • 187
  • is there a way to do it without making a temp table? – user867621 May 25 '12 at 14:21
  • You may be able to. Someone else may know better. You may be able to make an object that has some sort of array of the values from your select statement. But it'd probably be easier to just make it a temp table in memory or something. – kentcdodds May 25 '12 at 14:23
  • Try looking at [this answer](http://stackoverflow.com/questions/3075147/select-into-variable-in-mysql-declare-causes-syntax-error) – kentcdodds May 25 '12 at 14:23
  • I tried this and the same result create procedure _lookup_name (_no int) begin declare name varchar(100); select (fname || ' ' || lname) into name from table where no = _no; select name; end; – user867621 May 25 '12 at 14:28
  • This is because your select statement can return more than one name, and your name variable cannot hold more than one name. I recommend a temp table... – kentcdodds May 25 '12 at 14:32
0

maybe :

define var1 char(30);
define var2 char(30);
create procedure _lookup_name (_no int)
    select (fname || ' ' || lname) into var1,var2 from table where number = _no;
end procedure;
Jonnus
  • 2,988
  • 2
  • 24
  • 33
Rendon
  • 1