Mysql Stored Procedure Error 1064
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business mysql create stored procedure Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation error 1064 42000 Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up syntax error : 1064 , when creating a stored procedure up vote 1 down vote favorite 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)' mysql database stored-procedures syntax share|improve this question edited Jan 24 at 23:30 asked Jan 24 at 22:54 Sonia Mathews 206 I think it's because of the word "stored", you don't need it. You just need CREATE PROCEDURE –Phiter Fernandes Jan 24 at 22:59 Also there is a select with a comma in the line above the create procedure which idk what is doing there –Phiter Fernandes Jan 24 at 23:00 First, your syntax looks like a strange mixture of SQL Server and MySQL. Second, what is select * from parent_user, doing there? –Gordon Linoff Jan 24 at 23:02 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted 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 var
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Mysql stored procedure error 1064 up vote 0 down vote favorite I'm working on some code, and got stuck on this stored http://stackoverflow.com/questions/34982577/syntax-error-1064-when-creating-a-stored-procedure procedure problem... Have spent too long now looking at it, so please if someone can tell me where I am a noob with this. I get the following error: Error Code: 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 '@ownerid varchar(50); declare @active tinyint; declare @subuser tinyint; set ' at line 4 on this code: DELIMITER // http://stackoverflow.com/questions/11816434/mysql-stored-procedure-error-1064 USE `test`// DROP PROCEDURE IF EXISTS `getAccount`// CREATE DEFINER=`peter`@`%` PROCEDURE `getAccount`( IN cid VARCHAR(20)) BEGIN DECLARE cardid VARCHAR(50); DECLARE @ownerid VARCHAR(50); DECLARE @active TINYINT; DECLARE @subuser TINYINT; SET cardid = MD5( cid + SHA1(cid + 'a salt value')); SELECT @active = active, @ownerid = ownerid, @subuser = subuser FROM cards_tbl WHERE cardhash = cardid; IF @active = 1 THEN IF @subuser = 1 THEN SELECT subuser_m2s_tbl.name,subuser_m2s_tbl.image, user_saldo.saldo AS credits FROM subuser_m2s_tbl JOIN user_saldo ON subuser_m2s_tbl.subhash = user_saldo.userhash WHERE subuser_m2s_tbl.subhash = @ownerid; ELSE SELECT user_m2s_tbl.name,user_m2s_tbl.image, user_saldo.saldo AS credits FROM user_m2s_tbl JOIN user_saldo ON user_m2s_tbl.userhash = user_saldo.userhash WHERE user_m2s_tbl.userhash = @ownerid; END IF; END IF; END$$ DELIMITER ; Am new to the stored procedures, so it is properly something very simple..... Thanks in advance mysql stored-procedures share|improve this question asked Aug 5 '12 at 12:32 user1577339 312 try removing the @ in front of the variables.. –Randy Aug 5 '12 at 12:34 That actually got the procedure to run(thanks so far), but now I get the 3 variables as the result and nothing from one of the next 2 select statements, which was what I was expecting to get?!? Any help on that? –user1577339 Aug 5 '12 at 12:44 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote accepted You should use ':=' instead
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and http://stackoverflow.com/questions/639089/sql-syntax-error-when-creating-a-stored-procedure-in-mysql policies of this site About Us Learn more about Stack Overflow the http://stackoverflow.com/questions/10159580/mysql-error-1064-on-stored-procedure-definition company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes stored procedure a minute: Sign up SQL syntax error when creating a stored procedure in MYSQL up vote 5 down vote favorite 1 i have a hard time locating an error when trying to create a stored procedure in mysql. if i run every single line of the procedure independently, everything works just fine. CREATE PROCEDURE cms_proc_add_child (param_parent_id INT, param_name CHAR(255), param_content_type CHAR(255)) mysql stored procedure BEGIN SELECT @child_left := rgt FROM cms_tree WHERE id = param_parent_id; UPDATE cms_tree SET rgt = rgt+2 WHERE rgt >= @child_left; UPDATE cms_tree SET lft = lft+2 WHERE lft >= @child_left; INSERT INTO cms_tree (name, lft, rgt, content_type) VALUES (param_name, @child_left, @child_left+1, param_content_type); END i get the following (helpful) error: ERROR 1064 (42000): 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 '' at line 3 … i just don't know where to start debugging, as every single one of these lines is correct. any tips? mysql stored-procedures share|improve this question asked Mar 12 '09 at 15:14 Pierre Spring 4,05593642 add a comment| 3 Answers 3 active oldest votes up vote 17 down vote accepted As line 3 contains the first ; perhaps you have a problem with your delimiters. see http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html mysql> delimiter // mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -> END -> // Query
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Error 1064 on Stored Procedure Definition up vote 1 down vote favorite I am new to MySQL procedures. I am simply trying to run a cursor over a data set and for each row run a different procedure (one I happen to know works). I am getting error code 1064 on line three of the below: CREATE PROCEDURE `safecycle`.`sp_aggregateAllPORDaily` () BEGIN DECLARE done INT DEFAULT FALSE; DECLARE porID SMALLINT UNSIGNED; DECLARE cur1 CURSOR FOR SELECT ID FROM point_of_recycle; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; read_loop: LOOP FETCH cur1 INTO porID; IF done THEN LEAVE read_loop; END IF; CALL sp_aggregatePORDaily(porID); END LOOP; CLOSE cur1; END I have been banging my head against the wall for a while and would very much appreciate some help. mysql sql mysql-error-1064 share|improve this question edited Apr 15 '12 at 4:45 mu is too short 285k42554558 asked Apr 15 '12 at 4:43 Nik 3,67283465 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted You haven't changed your delimiter, so the first ; encountered terminates the entire statement. DELIMITER $$ CREATE PROCEDURE blah blah blah END$$ DELIMITER ; By changing the delimiter this way, you can safely define your multi-statement procedure, embedding the normal ; within it, and then end the create statement with the 'new'