title: SELECT INTO @var in Stored Procedure not working
body: <p>I have data coming in from an external source that when it comes is comma separated. I am creating a stored procedure that will be called from an already existing before insert trigger. The stored procedure will counts the number of commas in the string, loops that many iterations, grabbing the substring for that index, ie on iteration 2 for string "abc,def,ghi"  returns deg, then creates an entry in a join table IF what is returned is validated against a table of known good values. </p>
  
  <pre><code>DELIMITER $$
  DROP PROCEDURE IF EXISTS `create_isbn_catalog_join_table`$$
  CREATE PROCEDURE create_isbn_catalog_join_table(catalog VARCHAR(32),isbn CHAR(20))
  BEGIN
  SET @x = 1;
  -- a crappy way to count substr within a string
  SELECT (length(catalog)-length(replace(catalog,',',''))) INTO  @catalog_count;
  SET @catalog_count = @catalog_count + 1;
  REPEAT
  -- a crappy way to simulate php substr
  SET @cat = '';
  SELECT REPLACE(
            SUBSTRING(
              SUBSTRING_INDEX(catalog, ',', @x),
              LENGTH(
                  SUBSTRING_INDEX(catalog, ',',@x -1)
              ) + 1
            ),
    ',','') INTO @cat;
  
  set @cat1 = 'WX';
  set @valid_catalog = 0 ;
  
  
  IF EXISTS (select 1 from valid_catalogs where catalog = @cat)  THEN
  
      select count(*),@cat from valid_catalogs where catalog = @cat ;
      select count(*),@cat1 from valid_catalogs where catalog = @cat1 ;
  
    INSERT INTO iz_products_catalogs
     (`isbn`,`catalog`)
        VALUES
     (isbn,@cat);
  END IF;
  
  SET @x = @x + 1 ;
  
  UNTIL @x &gt; @catalog_count END REPEAT;
  END$$
  
  DELIMITER ;
  </code></pre>
  
  <p>Whats very odd here is the following i incorrectly am passing the IF clause to determine if the current catalog in the loop is valid or not; exemplified by the 2 querys that are being ran:</p>
  
  <pre><code>select count(*),@cat from valid_catalogs where catalog = @cat ;
  select count(*),@cat1 from valid_catalogs where catalog = @cat1 ;
  </code></pre>
  
  <p>Note @cat is from the substring select and that @cat1 is set via a SET command. Both are 2 char values that are not in the valid catalog table. The return value is:</p>
  
  <pre><code>+----------+------+
  | count(*) | @cat |
  +----------+------+
  |       27 | WZ   |
  +----------+------+
  1 row in set (0.00 sec)
  
  +----------+-------+
  | count(*) | @cat1 |
  +----------+-------+
  |        0 | WX    |
  +----------+-------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Both WZ or WX are not in the valid_catalogs table. But when querying the count of from valid catalogs using @cat, it returns 27 (which is the total number of rows in the table) which should be zero as there is not a 'WZ' row. when querying with @cat1 it accurately returns 0. </p>