Member Avatar for DGULLIVER

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
  • 6K Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas 
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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.