[neuroticbiscuit]

How do I maintain the value of a variable beyond the first line of a procedure?

I want to use:

CREATE PROCEDURE IntSetUp (a_var VARCHAR(100))
insert into db1.table1
(valueA, valueB)
SELECT a.value, b.value
FROM db2.table1 a inner join db2.table2 b on a.id=b.id
where a.value=a_var;
INSERT into db1.table2
(valueA, valueB)
SELECT a.valueA, a.valueB
FROM db2.table1 a where a.value=a_var;

but the variable loses is value after the first INSERT line, so the 2nd insert never gets done.

I've tried replacing the top line with:

CREATE PROCEDURE IntSetUp (a_var VARCHAR(100))
SET @x := a_var;
and then
SET @x := a_var;

and I got the same problem, the values just disapper after the first line.

I then tried enclosing the whole thing in BEGIN and END tags and putting:
DECLARE @x = a_var
as the first line (also tried DECLARE x = a_var)

but this just throws up an error.

What am I doing wrong?

[aschk]

You haven't created the stored procedure correctly. The first time you have done it the first SQL line stops at ; because you have failed to specify a delimiter.

In your second attempt you are indeed correct in specifying a BEGIN/END clause as this is what you should be doing.

However what you are mostly likely to want is as follows :

delimiter //

CREATE PROCEDURE IntSetUp (IN a_var VARCHAR(100))
BEGIN
  SELECT a.value, b.value
  FROM db2.table1 a inner join db2.table2 b on a.id=b.id
  where a.value=a_var;

  INSERT into db1.table2
  (valueA, valueB)
  SELECT a.valueA, a.valueB
  FROM db2.table1 a where a.value=a_var;
END;
//

delimiter ;

All of the above wants to be specified at cmd line.