Mysql Error 1064 Stored Procedure
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 mysql create stored procedure about Stack Overflow the company Business Learn more about hiring developers or posting ads error 1064 42000 with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow mysql stored procedure is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Create Procedure Yields Error 1064 up vote 0 down vote favorite 1 Good Morning, I'm creating the following procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_inventory_audit`( IN `pitem_id` int, IN `puser_id` int, IN `pfield_name` varchar(265), IN `pold_value` mediumtext, IN `pnew_value` mediumtext ) BEGIN INSERT INTO inventory_audit (item_id, user_id, field_name, old_value, new_value) VALUES (pitem_id, puser_id, pfield_name, pold_value, pnew_value); END$$ It is being copied to our new server running MySQL 5.5.19 from our old server running MySQL 5.0.45. When I excecute the above code on the new server, I recieve the following 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 '' at line 11 Does this mean that each entry inside the VALUES parentheses must be surrounded by '' eg. 'pitem_id' ? mysql mysql-error-1064 share|improve this question asked Jan 4 '12 at 18:42 SidC 1,44384295 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted You need to have DELIMTER $$ before the create statement. share|improve this answer answered Jan 4 '12 at 18:47 kcdragon 1,274715 add a comment| up vote 2 down vote You didn't change the delimiter from the default ;, so the ; you're using there is actually terminating the procedure, not the query. DELIMITER $$ <--- add this line CREATE .... ... END$$ share|improve this answer answered Jan 4 '12 at 18:47 Marc B 273k24233352 Thnx This works for me –Armaan Dec 18 '14 at 14:34 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Nam
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: http://stackoverflow.com/questions/8732137/mysql-create-procedure-yields-error-1064 Sign up Error #1064 in stored procedure in mysql up vote -1 down vote favorite if cant to understand my english is because my english is bad.... i will try to explain my problem as best as possible. I try to create a stored procedure in mysql (phpmyadmin) but i have this error: #1064 - You have an error in your SQL http://stackoverflow.com/questions/31225207/error-1064-in-stored-procedure-in-mysql syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 This is the code of stored procedure: CREATE PROCEDURE `sp_user_insert` (IN `sp_email` varchar(255), IN `sp_password` text, IN `sp_role` int) BEGIN DECLARE `sp_encrypted_password` text; SET `sp_encrypted_password` = SELECT MD5(`sp_encrypted_password`); INSERT INTO `user`( `user_email`, `user_password`, `user_role_id`) VALUES ( `sp_email`, `sp_encrypted_password`, `sp_role`); END And this is the code of the table user: CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_email` varchar(255) NOT NULL, `user_password` text NOT NULL, `user_role_id` int(11) NOT NULL, PRIMARY KEY (`user_id`) ); PD: I'm sorry, my english is bad :C mysql stored-procedures phpmyadmin share|improve this question asked Jul 4 '15 at 21:22 Nac-Ho 42 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted This will help. Try once. DELIMITER $$; CREATE PROCEDURE sp_user_insert (IN sp_email varchar(255), IN sp_password TEXT, IN sp_role INT(20)) BEGIN DECLARE sp_encrypted_password TEXT; SELECT MD5(sp_password) INTO sp_encrypted_password; INSERT INTO user( user_email, user_password, user_role_id) VALUES ( sp_email, sp_encrypted_password, sp_role); END share|improve this answer edited Jul 4 '15 at 21:58
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 http://stackoverflow.com/questions/20107342/mysql-error-1064-creating-a-stored-procedure site About Us Learn more about Stack Overflow the company Business Learn http://stackoverflow.com/questions/10159580/mysql-error-1064-on-stored-procedure-definition 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 Creating a Stored Procedure up vote 1 down vote favorite Trying to import this stored procedure via a .SQL file. I keep getting this 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 ') BEGIN SELECT * FROM Cars WHERE Cars.Color = varCarColor mysql error 1064 END' at line 1 You can see my SQL code below. I've tried adding $$ after the END then a DELIMITER ; but it still won't import. DELIMITER $$ CREATE PROCEDURE `CarDealership`.`SelectCarByColor`(varCarColor VARCHAR(25)) BEGIN SELECT * FROM Cars WHERE Cars.Color = varCarColor; END mysql sql share|improve this question edited Nov 21 '13 at 4:58 Isaac Bennetch 4,8971821 asked Nov 20 '13 at 21:24 Matt 98419 1 The VARCHAR data type requires you to specify the maximum length. –eggyal Nov 20 '13 at 21:30 Thanks @eggyal I added the length but still no luck –Matt Nov 20 '13 at 21:33 3 You also need to terminate the SELECT statement with a ; statement delimiter (i.e. between varCarColor and END). Either that, or remove the BEGIN ... END compound statement block, seeing as this procedure contains only a single statement (which would also entirely avoid you having to change statement delimiter). –eggyal Nov 20 '13 at 21:35 Figured it out needed the a semicolon ; after the last SQL statement –Matt Nov 20 '13 at 21:38 2 Isn't that what I just said? &nda
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' delimiter. Afterwards, you restore the standard delimiter and go on as usual. share|improve this answer answered Apr 15 '12 at 4:45 Marc B 273k24233352 Yes, duh, thank you. –Nik Apr 15 '12 at 4:48 Very weird of sqlyog to generate Delimiter with ';' in the starting, which was actually terminating the entire statement perhaps, when run from comman