CODEWITHSUNDEEP
mysqlsqlstored-procedures
vlio20
vlio20

Reputation: 9295

MySQL stored procedure @ syntax error

I want to be able to pass arguments to stored procedure, so I searched the net and encountered something like this:

DELIMITER $$
CREATE PROCEDURE addTmpUser 
@id varchar(10)
AS
BEGIN
    //some sql code
END$$

DELIMITER ;

The problem is that I am getting a syntax error for the @ character.

Note: I am using MySQL db.

Upvotes: 0

Views: 97

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40001

You are mixing variable types.
@variable is a user variable with a scope for the entire connection.
The variables in stored procedures look different, they don't have the @ before them.

Also, you need to declare them. Here is an example

DELIMITER $$
CREATE PROCEDURE addTmpUser(p_id varchar(10))
-- the variable is named p_id as a nameing convention. 
-- It is easy for variables to be mixed up with column names otherwise.
BEGIN
    DECLARE innerVariable int;
    insert into user (id) values (p_id);
    -- return all users
    select * from user;
END$$

DELIMITER ; 

-- and now call it
call addTmpUser(10);

Upvotes: 2

Sachin
Sachin

Reputation: 40970

You need to use IN,OUT,INOUT to specify the parameter. So you can try this

DELIMITER $$
CREATE PROCEDURE addTmpUser (IN id VARCHAR(10))
BEGIN
    //some sql code
END$$

DELIMITER ;

Look at the documentation

Upvotes: 2

Related Questions