Inserting newly created Primary Key from one table into another via stored procedure

tbinkley
tbinkley used Ask the Experts™
on
Hello,

I was just wondering if something I was looking to do was doable or not.

I have a stored procedure that will first insert a record into a table (MO_ITEMS) and increment the primary key/generator accordingly.

I then want to take the newly created PK and insert that into another table which will be used as a cross reference (MO_ITEM_CATS).

Here is what I have so far to create the procedure and a trigger for the generator:

CREATE GENERATOR ITEM_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER CREATE_ITEM_ID FOR MO_ITEMS
   BEFORE INSERT
   POSITION 0
   AS BEGIN
         NEW.ITEM_ID = GEN_ID(ITEM_ID_GEN, 1);
   END^

CREATE PROCEDURE NEW_ITEM
(
  VendorID  INTEGER,
  CatID          INTEGER,
  ItemName  VARCHAR(50),
  Details   BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
  Cost      NUMERIC(4,2),
  ShowMain  SMALLINT,
  IsActive  SMALLINT
)
AS
BEGIN
  INSERT INTO MO_ITEMS (ITEM_ID, VENDOR, ITEM_NAME, DETAILS, COST, SHOW_ON_MAIN, IS_ACTIVE)
  VALUES (0, :VendorID, :ItemName, :Details, :Cost, :ShowMain, :IsActive);
  INSERT INTO MO_ITEM_CATS (ITEM_ID, CAT_ID) VALUES (:ItemID, :CatID);
END^
SET TERM ; ^

Will I need to set an output parameter for the ITEM_ID or is there a quicker way.  I know SQL Server has the @@IDENTITY property and I'd like to do something like that.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Principal Operations Engineer
Commented:
this is a fairly common situation, you need to get and hold the new pk value so you can use it in both second inserts. The trigger is not strictly necessary in this solution but it's not a bad idea, especailly if rows will be inserted into mo_items without calling the procedure.

NB syntax not checked

CREATE GENERATOR ITEM_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER CREATE_ITEM_ID FOR MO_ITEMS
   BEFORE INSERT
   POSITION 0
   AS BEGIN
     if (new.item_id is null or new.item_id < 1)
        NEW.ITEM_ID = GEN_ID(ITEM_ID_GEN, 1);
   END^

CREATE PROCEDURE NEW_ITEM
(
  VendorID  INTEGER,
  itemid integer,
  CatID         INTEGER,
  ItemName  VARCHAR(50),
  Details   BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
  Cost      NUMERIC(4,2),
  ShowMain  SMALLINT,
  IsActive  SMALLINT
)
AS
BEGIN
  itemid = GEN_ID(ITEM_ID_GEN, 1);
  INSERT INTO MO_ITEMS (ITEM_ID, VENDOR, ITEM_NAME, DETAILS, COST, SHOW_ON_MAIN, IS_ACTIVE)
  VALUES (:itemid, :VendorID, :ItemName, :Details, :Cost, :ShowMain, :IsActive);
  INSERT INTO MO_ITEM_CATS (ITEMID CAT_ID) VALUES (:itemid, :CatID);
END^
SET TERM ; ^

Author

Commented:
Thanks for the reply, Nick.

That looks like a solid solution.  I'll give it a try and let you know how it goes.

Author

Commented:
I recreated my stored procedure as suggested, but I keep getting this error from my ASP page when I try to add an item:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/marketing/admin/add_item.asp, line 40

Here is the ASP code I'm using:

itemName = request.Form("itemName")
itemDetails = request.Form("itemDetails")
vendorID = request.Form("vendorID")
itemCat = request.Form("catID")
showMain = request.Form("showMain")
isActive = request.Form("isActive")
itemCost = request.Form("itemCost")
      
with objCmdItem
  .ActiveConnection = objConn
  .CommandText = "NEW_ITEM"
  .CommandType = adCmdStoredProc
        
  .Parameters.Append .CreateParameter(":ItemID", adInteger, adParamInput, , 0)
  .Parameters.Append .CreateParameter(":VendorID", adInteger, adParamInput, , vendorID)
  .Parameters.Append .CreateParameter(":CatID", adInteger, adParamInput, , catID)
  .Parameters.Append .CreateParameter(":ItemName", adVarChar, adParamInput, 50, itemName)
  .Parameters.Append .CreateParameter(":ItemDetails", adLongVarChar, adParamInput, 2147483647, itemDetails)
  .Parameters.Append .CreateParameter(":Cost", adNumeric, adParamInput, , itemCost)
  .Parameters.Append .CreateParameter(":ShowMain", adSmallInt, adParamInput, , showMain)
  .Parameters.Append .CreateParameter(":IsActive", adSmallInt, adParamInput, , isActive)
        
  .Execute, , adExecuteNoRecords
        
end with

I can get this code to work with other stored procedures that have only one line of SQL, but I've been getting the error posted above with each multi step operation.  Will I have to break them apart into separate stored procedures (which I'd rather not do)?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Sorry, forgot this was the Interbase forum and not ASP.  If anyone can help with the above post, cool.  If not, I'll just try the ASP forum.
Nick UpsonPrincipal Operations Engineer

Commented:
the error may be due to specifying itemid as an arg instead of an variable or calling them in the wrone order, the list in your asp code is not in the same order as the
definitions (I don't know ASP)

CREATE PROCEDURE NEW_ITEM
(
  VendorID  INTEGER,
   CatID         INTEGER,
  ItemName  VARCHAR(50),
  Details   BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
  Cost      NUMERIC(4,2),
  ShowMain  SMALLINT,
  IsActive  SMALLINT
)
 itemid integer
First try your procedure if work from an external utility.
Order of parameters is also important or specify ".NamedParameters = true"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial