Hi,

I want to create a SP in mySQL to insert records and return the Primary Key. But when I try and create the SP I get error:
mysql said #1064, You have an error in your SQL Syntax, Check the manual that corresponds to your MySSQL server version for the right syntax to use near "SET @UG_ID = LAST_INSERT_ID() at line 3 here is my sql

CREATE DEFINER =  `root`@`localhost` PROCEDURE  `SPNEWUG` ( IN  `@GroupName` VARCHAR( 255 ) , IN  `@HeadOffice` TINYINT( 2 ) , OUT `@UG_ID` INT( 8 ) ) COMMENT  'Add New User Group' NOT DETERMINISTIC NO SQL SQL SECURITY INVOKER 
INSERT INTO tblUserGroup( tblUserGroup.UG_Name, tblUserGroup.UG_GBTF_HO ) 
VALUES (@GroupName , @HeadOffice);
SET @UG_ID = LAST_INSERT_ID();

Table Structure:

Field Type Null Key Default Extra
UG_ID int(8) NO PRI NULL auto_increment
UG_Name varchar(30) YES NULL
UG_GBTF_HO tinyint(2) NO NULL

I based this on this thread:
http://stackoverflow.com/questions/13151861/fetch-last-inserted-id-form-stored-procedure-in-mysql

I've seen other threads that use this approach but unsure why it not working for me.

Cheers
Darren

CREATE DEFINER =  `root`@`localhost` PROCEDURE  `SPNEWUG` 
( 
    IN  `@GroupName` VARCHAR( 255 ) , 
    IN  `@HeadOffice` TINYINT( 2 ) , 
    OUT `@UG_ID` INT( 8 ) 
) 
COMMENT  'Add New User Group' 
NOT DETERMINISTIC NO SQL SQL SECURITY INVOKER 
BEGIN
    INSERT INTO tblUserGroup( tblUserGroup.UG_Name, tblUserGroup.UG_GBTF_HO ) VALUES (@GroupName , @HeadOffice);
    SET @UG_ID = LAST_INSERT_ID();
END

That worked for me. Two lines, so you need a block.