469,160 Members | 2,063 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

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 3867
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.