Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1376
  • Last Modified:

Stored Procedure Using LAST_INSERT_ID()

Im trying to use LAST_INSERT_ID() as variable in a stored procedure
after the first insert statment is run i want to return the id of the last auto inc record and then use that value in the second insert statement.
attached is my current procedure it returns the following error:
Unknown column 'NewLockID' in 'field list'




CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_LockAdd`(
IN LockSerial VarChar(50),
IN LockDescription VarChar(50),
IN LockAssetID VarChar(50),
IN LockName VarChar(50),
IN LockLabelID VarChar(45),
IN LockAdded VarChar(45),
IN LockAddedBy VarChar(45),
IN LockStreet VarChar(45),
IN LockCity VarChar(45),
IN LockState VarChar(45),
IN LockZip VarChar(45),
IN LockCountry VarChar(45),
IN RouteID INT (11))
BEGIN
INSERT INTO esc.tbllocks (fldIDElectronic,fldDescription,fldAssetID,fldName,fldIDLabel,fldadded,fldaddedby,fldaddress1,fldcity,fldstate,fldzip,fldcountry) VALUES
(LockSerial,LockDescription,LockAssetID,LockName,LockLabelID,LockAdded,LockAddedBy,LockStreet,LockCity,LockState,LockZip,LockCountry);
SELECT LAST_INSERT_ID() as NewLockID;
INSERT INTO tblroutestolocks (fldLockID,fldRouteID) VALUES
(NewLockID,RouteID);
END

Open in new window

0
philosullivan
Asked:
philosullivan
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_LockAdd`(
IN p_LockSerial VarChar(50),
IN p_LockDescription VarChar(50),
IN p_LockAssetID VarChar(50),
IN p_LockName VarChar(50),
IN p_LockLabelID VarChar(45),
IN p_LockAdded VarChar(45),
IN p_LockAddedBy VarChar(45),
IN p_LockStreet VarChar(45),
IN p_LockCity VarChar(45),
IN p_LockState VarChar(45),
IN p_LockZip VarChar(45),
IN p_LockCountry VarChar(45),
IN p_RouteID INT (11))
BEGIN
INSERT INTO esc.tbllocks (fldIDElectronic,fldDescription,fldAssetID,fldName,fldIDLabel,fldadded,fldaddedby,fldaddress1,fldcity,fldstate,fldzip,fldcountry) VALUES
(p_LockSerial,p_LockDescription,p_LockAssetID,p_LockName,p_LockLabelID,p_LockAdded,p_LockAddedBy,p_LockStreet,p_LockCity,p_LockState,p_LockZip,p_LockCountry);
INSERT INTO tblroutestolocks (fldLockID,fldRouteID) SELECT LAST_INSERT_ID() ,p_RouteID;
END

Open in new window

0
 
philosullivanAuthor Commented:
awsome, thanks! i see what i was doing wrong.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now