MySQL Bugs: #44667: Script line: 1 You have an error in your SQL syntax; check the manual that corre

Bug #44667	Script line: 1 You have an error in your SQL syntax; check the manual that corre
Submitted:	5 May 2009 12:00	Modified:	6 May 2009 5:11
Reporter:	riju oommen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Error in creating stored procedure

Description:
CREATE PROCEDURE payment
(
payment_amount DECIMAL(6,2),
payment_seller_id INT
)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = 5 - 1.00;
INSERT INTO test VALUES (n, 'a');
END;

while creating the above procedure iam getting below mentioned error

Script line: 1	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 7

How to repeat:
r

Suggested fix:
r

Which version of MySQL are you using? I'm unable to reproduce this on 5.1.30.
Did you change the delimiter as described in:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

DELIMITER //
CREATE PROCEDURE `shahcoal`.`riju` (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t
END ;
//
DELIMITER ;

Script line: 2	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 4

Thank you for the bug report. Please read the Manual about the stored procedure syntax:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.82-Win X64 revno 2778-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > create database d7;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use d7
Database changed
mysql 5.0 > create table test (col1 decimal(15,2), col2 char(1));
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > delimiter $$
mysql 5.0 > CREATE PROCEDURE payment
    -> (
    -> payment_amount DECIMAL(6,2),
    -> payment_seller_id INT
    -> )
    -> BEGIN
    -> DECLARE n DECIMAL(6,2);
    -> SET n = 5 - 1.00;
    -> INSERT INTO test VALUES (n, 'a');
    -> END$$
Query OK, 0 rows affected (0.05 sec)

MySQL Server 5.1

Try this it should work.

DELIMITER //
CREATE PROCEDURE `shahcoal`.`riju` (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t
END//

DELIMITER ;

DELIMITER //
CREATE PROCEDURE `shahcoal`.`riju` (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM test;
END//

DELIMITER ;

Script line: 2	Cannot create stored routine `riju`. Check warnings

DELIMITER $$

DROP PROCEDURE IF EXISTS `shahcoal`.`riju` $$
CREATE PROCEDURE `shahcoal`.`riju` ()
BEGIN

END $$

DELIMITER ;

ON EXECUTING ABOVE PROC IAM GETTING BELOW MENTIONED ERROR:-

Script line: 4	Cannot create stored routine `riju`. Check warnings

WHERE I CAN CHK WARNINGS AS IAM NEW TO MYSQL.

We're sorry, but the bug system is not the appropriate forum for asking
help on using MySQL products. Your problem is not the result of a bug and to get the warnings you can use:

http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html

Support on using our products is available both free in our forums at
http://forums.mysql.com/ and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.