Stored procedure won"t allow multiple commands

Stored procedure won"t allow multiple commands

am 17.08.2006 00:10:03 von deja.10.thrillho

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_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.

Re: Stored procedure won"t allow multiple commands

am 17.08.2006 00:31:47 von deja.10.thrillho

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_menu,header_ menu,version)
VALUES
(_name,_description,_header,_footer,_left_menu,_right_menu,_ header_menu,_version
);

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