Bug #18411 Unable to create what I believe is a valid stored procedure.

• Submitted: 22 Mar 2006 6:54
• Modified: 22 Mar 2006 8:56
• Reporter: Hasani Blackwell
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S2 (Serious)
• Version: 5.0.18
• OS: Windows (Win2k)
• CPU Architecture: Any

[22 Mar 2006 6:54] Hasani Blackwell

Description:
I was creating a stored procedure and was unable to get the following stored procedure to compile.

CREATE PROCEDURE add_shipping_info
(
	IN email_template_name_ VARCHAR(255),
	IN invoice_no_ VARCHAR(255),
	IN order_no_ VARCHAR(255),
	IN reference_no_ VARCHAR(255),
	IN shipping_co_ VARCHAR(255),
	IN tel_no_ VARCHAR(255),
	IN url_ VARCHAR(255),
	IN tracking_no_ VARCHAR(255)
)
BEGIN
	INSERT INTO `shipping_info`(`email_template_name`, `invoice_no`, `order_no`, `reference_no`, `shipping_co`, `tel_no`, `url`)
	VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL);

	COMMIT;
	
	DECLARE barfs_here INT;
	SELECT 12;
END;

It works If I remove the INSERT INTO + COMMIT statement - XOR - I remove the DECLARE statement.

I dunno why, I really hope I'm doing something wrong, but it looks like valid sql.

Here's the exact output
-------------------------------------------------------------------------------

3/22/2006 1:53:39 AM[MySqlDataProviderTester.exe] HB!HB.Data.Helper.GenericDbCommandHelper.ExecuteReader: [mysql5 test server] ExecuteReader:

CREATE TABLE IF NOT EXISTS `shipping_info` (
  `id` int(11) NOT NULL auto_increment,
  `email_template_name` varchar(255) default NULL,
  `invoice_no` varchar(255) default NULL,
  `order_no` varchar(255) default NULL,
  `reference_no` varchar(255) default NULL,
  `shipping_co` varchar(255) default NULL,
  `tel_no` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS add_shipping_info;

CREATE PROCEDURE add_shipping_info
(
        IN email_template_name_ VARCHAR(255),
        IN invoice_no_ VARCHAR(255),
        IN order_no_ VARCHAR(255),
        IN reference_no_ VARCHAR(255),
        IN shipping_co_ VARCHAR(255),
        IN tel_no_ VARCHAR(255),
        IN url_ VARCHAR(255),
        IN tracking_no_ VARCHAR(255)
)
BEGIN
        INSERT INTO `shipping_info`(`email_template_name`, `invoice_no`, `order_no`, `reference_no`, `shipping_co`, `tel_no`, `url`)
        VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL);

        COMMIT;

        DECLARE barfs_here INT;
        SELECT 12;
END;
HB.MySql.MySqlStandardApiException: 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 'DECLARE barfs_here INT;
        SELECT 12;
END' at line 17
   at HB.MySql.MySqlHandle.CheckForError()
   at HB.MySql.MySqlHandle.NextResult()
   at HB.MySql.MySqlStandardDataReader.NextResult()
   at HB.Data.GenericDbDataReader.NextResult() in C:\Documents and Settings\hasani\My Documents\Visual Studio Projects\MySqlDataProviderTester\HB\Data\GenericDbDataReader.cs:line 130
   at MySqlDataProviderTester.EntryPoint.Main(String[] args) in c:\documents and settings\hasani\my documents\visual studio projects\mysqldataprovidertester\mysqldataprovidertester\entrypoint.cs:line 92
Press any key to continue

How to repeat:
run the following sql

CREATE TABLE IF NOT EXISTS `shipping_info` (
  `id` int(11) NOT NULL auto_increment,
  `email_template_name` varchar(255) default NULL,
  `invoice_no` varchar(255) default NULL,
  `order_no` varchar(255) default NULL,
  `reference_no` varchar(255) default NULL,
  `shipping_co` varchar(255) default NULL,
  `tel_no` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS add_shipping_info;

CREATE PROCEDURE add_shipping_info
(
	IN email_template_name_ VARCHAR(255),
	IN invoice_no_ VARCHAR(255),
	IN order_no_ VARCHAR(255),
	IN reference_no_ VARCHAR(255),
	IN shipping_co_ VARCHAR(255),
	IN tel_no_ VARCHAR(255),
	IN url_ VARCHAR(255),
	IN tracking_no_ VARCHAR(255)
)
BEGIN
	INSERT INTO `shipping_info`(`email_template_name`, `invoice_no`, `order_no`, `reference_no`, `shipping_co`, `tel_no`, `url`)
	VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL);

	COMMIT;
	
	DECLARE barfs_here INT;
	SELECT 12;
END;

[22 Mar 2006 8:44] Hartmut Holzgraefe

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.

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.

Additional info:

See http://dev.mysql.com/doc/refman/5.0/en/declare.html:

"DECLARE is allowed only inside a BEGIN ... END compound statement and must be at its start, *before* any other statements."

[22 Mar 2006 8:56] Hasani Blackwell

wow, sorry on my part!!!