Stored procedure won"t allow multiple commands
am 17.08.2006 00:10:03 von deja.10.thrillhoI 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_menu,header_ menu,version)
VALUES
(_name,_description,_header,_footer,_left_menu,_right_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.