472,122 Members | 1,509 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,122 software developers and data experts.

Stored procedure won't allow multiple commands

I am try to create a procedure that does inserts and returns a command:

DELIMITER $$

DROP PROCEDURE IF EXISTS `testDB`.`insmod` $$
CREATE PROCEDURE `insmod`(
_name VARCHAR(50)
,_description TEXT
,_header varchar(50)
,_footer VARCHAR(50)
,_left_menu varchar(50)
,_right_menu varchar(50)
,_header_menu varchar(50)
,_version varchar(10)
,OUT module_id INT
)
INSERT INTO jd_module
(name,description,header,footer,left_menu,right_me nu,header_menu,version)
VALUES
(_name,_description,_header,_footer,_left_menu,_ri ght_menu,_header_menu,_version)
$$

SET module_id = @@IDENTITY $$

DELIMITER ;

When I run this script to create the procedure, this message is shown:
"Unknown system variable 'module_id'"
BUT, it is created and I can run it like so:

call insmod(now(),'','','','','','','',@mod_id);
select @mod_id;

However, @mod_id is always NULL. Please share some insight. I based
this off of the docs here:
http://mysql.com/doc/refman/5.0/en/call.html, but I can't even create
the sproc in the example.

MySQL version: 5.0.15-nt

Thanks.

Aug 16 '06 #1
1 3919
FIXED IT. FYI, this works:

DELIMITER $$
CREATE PROCEDURE `insmod`(
_name VARCHAR(50)
,_description TEXT
,_header varchar(50)
,_footer VARCHAR(50)
,_left_menu varchar(50)
,_right_menu varchar(50)
,_header_menu varchar(50)
,_version varchar(10)
,OUT module_id INT
)
BEGIN
INSERT INTO jd_module
(name,description,header,footer,left_menu,right_me nu,header_menu,version)
VALUES
(_name,_description,_header,_footer,_left_menu,_ri ght_menu,_header_menu,_version
);

SET module_id = @@IDENTITY;
END $$
DELIMITER ;

Aug 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Lauren Quantrell | last post: by
7 posts views Thread by Jeff Wang | last post: by
8 posts views Thread by Thomasb | last post: by
3 posts views Thread by John F | last post: by
10 posts views Thread by J. S. EDV | last post: by
2 posts views Thread by jed | last post: by
reply views Thread by pompeyoc | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.