how to pass parameter of stored procedure to the selected list?

Question

i have the following mysql Stored procedure

create procedure SP_InsertTag_Level2 (tag_v varchar(50),  CNT_v int)
      select tweet_id into @tid from tweet_tags where tag=tag_v;
      insert into collected_tags (tag,country) select tag_v, A.country from collected_tags A, tweet_tags B where A.tag=B.tag and B.tweet_id=@tid;
      select id into @Id from collected_tags where tag=tag_v;
      IF @Id IS NOT NULL THEN
        insert into stats_tag(id,counter) values (@id,CNT_v);
      END IF;
      end;

now i am getting error ERROR 1054 (42S22): Unknown column 'tag_v' in 'field list' for the bolded line above since tag_v is not a column in collected_tags table (it is parameter in the SP)
how can i keep (insert) a variable in the selected list of columns in the stored procedure?

Johan Johan · Accepted Answer · 2011-10-10T16:09:04

You need to start the stored procedure body with a BEGIN.
Also I'd recommend against using @vars in a stored procedure, because they leak to the outside. Better to used typed declared vars.

In order to successfully close a stored proc, you need to declare a custom delimiter.
And use that to close the final END

DELIMITER $$

CREATE PROCEDURE SP_InsertTag_Level2 (IN tag_v varchar(50), IN CNT_v int)
BEGIN
  DECLARE tid INTEGER;
  DECLARE myid INTEGER;
  SELECT tweet_id INTO tid FROM tweet_tags WHERE tag=tag_v;
  INSERT INTO collected_tags (tag,country) 
     SELECT tag_v, A.country 
     FROM collected_tags A
     INNER JOIN tweet_tags B on (A.tag = B.tag)
     WHERE B.tweet_id = tid
  SELECT id INTO myId FROM collected_tags WHERE tag=tag_v;
  IF (myId IS NOT NULL) THEN
    INSERT INTO stats_tag(id,counter) VALUES (myid,CNT_v);
  END IF;
END $$

DELIMITER ;

Implicit join's are evil
Do not use implicit joins, they are an anti-pattern, use explicit join syntax instead.

how to pass parameter of stored procedure to the selected list?

1 Answers