Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    1

    Unanswered: using variables in mysql

    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?

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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 :
    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •