Syntax Error : 1064 , When Creating A Stored Procedure
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)'
This is invalid syntax for MySQL Stored Procedure. The code you posted looks more like Microsoft SQL Server (Transact SQL) syntax.
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.
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 ;
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key