0

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

2
Contributors
1
Reply
10
Views
2 Years
Discussion Span
Last Post by pritaeas
0
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.

Edited by pritaeas

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.