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)'

Answer 1

The code in the question is invalid syntax for MySQL Stored Procedure. It looks more like Microsoft SQL Server (Transact SQL) syntax.

Some observations:

MySQL procedure variables cannot start with @ because that character is reserved for user-defined variables.

MySQL doesn't use a NVARCHAR type. 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 (statement terminators).

The INSERT is for a table with four columns; there are only three values and no column list.

If the goal is to create a stored procedure in MySQL, we'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 ;