syntax error : 1064 , when creating a stored procedure

Question

CREATE table parent_user
 ( userid int  auto_increment PRIMARY KEY,
 Username varchar(100) NOT NULL,
    Password varchar(200) NOT NULL,
    Email varchar(200) NOT NULL

 );

EDIT : OK so I made some changes:

CREATE PROCEDURE `parent_reg` (
pUserName varchar(100)
pPassword varchar(200)
pEmail varchar(200)
)
as
Begin
Declare Count int
Declare ReturnCode int

Select Count = Count(Username)
from parent_user where Username = @Username
If Count > 0
Begin
      Set ReturnCode = -1
    End
    Else
    Begin 
      Set ReturnCode = 1
      insert into parent_user values
      (pUserName, pPassword, pEmail)
    End
    Select pReturnCode as ReturnValue 
    End

But I still got the same error- Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pPassword varchar(200) pEmail varchar(200) ) ....'

The syntax error is at 'pPassword varchar(200)'

I think it's because of the word "stored", you don't need it. You just need CREATE PROCEDURE — Phiter
Also there is a select with a comma in the line above the create procedure which idk what is doing there — Phiter
First, your syntax looks like a strange mixture of SQL Server and MySQL. Second, what is select * from parent_user, doing there? — Gordon Linoff

spencer7593 spencer7593 · Accepted Answer · 2016-01-24T23:05:54

This is invalid syntax for MySQL Stored Procedure. The code you posted looks more like Microsoft SQL Server (Transact SQL) syntax.

Some observations:

MySQL procedure variables cannot start with @. (That character is reserved for user-defined variables.)

MySQL doesn't use a NVARCHAR type. I believe it's the setting of the character_set_client variable in the session (at the time the procedure is created) is what controls the characterset of the procedure variables.

The line select * from parent_user, before the CREATE PROCEDURE looks entirely out of place.

Missing semicolons. The INSERT is for a table with four columns, but only three values, and there's no column list. Lots of other oddly bizarre syntax.

If your goal is to create a stored procedure in MySQL, you'd need syntax closer to this:

DELIMITER $$

CREATE PROCEDURE parent_reg(p_username VARCHAR(100),
p_password VARCHAR(200), p_email VARCHAR(200)
)
BEGIN
  DECLARE mycount INT;
  DECLARE myreturncode INT;

  SELECT COUNT(pu.username)
    INTO mycount
    FROM `parent_user` pu
   WHERE pu.username = p_username;

  IF (mycount > 0 ) THEN
    SET myreturncode = -1;
  ELSE
    SET myreturncode = 1;
    INSERT INTO `parent_user` (`username`, `password`, `email`)
    VALUES (p_username, p_password, p_email);
  END IF;
  SELECT myreturncode AS `ReturnValue`;
END$$

DELIMITER ;

syntax error : 1064 , when creating a stored procedure

2 Answers