MySQL stored procedure: variable in WHERE clause?

Question

Can you not do the following w/ MySQL stored procedures?/

DROP PROCEDURE IF EXISTS `test`;
DELIMITER //

CREATE PROCEDURE TEST (team varchar(30))
BEGIN
 SELECT * FROM TEAMS WHERE TEAM_ID = @team;
END
//

Where @team (or team) is the variable passed to the stored procedure?

Answer 1

Well, unlike CREATE FUNCTION, in CREATE PROCEDURE, you need to specify a parameter as either IN or OUT

This should work for you:

DROP PROCEDURE IF EXISTS TEST;
DELIMITER //

CREATE PROCEDURE TEST (IN team varchar(30))
BEGIN
        SELECT * FROM TEAMS WHERE TEAM_ID = team;
END
//

More info on CREATE PROCEDURE here

Answer 2

You need to use:

DROP PROCEDURE IF EXISTS `test`;
DELIMITER //

CREATE PROCEDURE TEST (IN_TEAM_ID varchar(30))
BEGIN

    SELECT t.* 
      FROM TEAMS t
     WHERE t.team_id = IN_TEAM_ID;

END

There's no @ notation when referencing stored procedure parameters.