Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Raleigh, NC, USA
    Posts
    19

    Unanswered: Passing Index Value

    I am attempting to string together insert statements in a stored procedure...

    In tblCatalogue there is all the information on catalogues.

    In tblArticles there is all the information on articles.

    There is a key between the articles table and the catalogues, that reflect which catalogue a specific article belongs in.

    I am trying to add a new article and column at the same time in a stored procedure. If a new record is created in the Catalogue table can that KeyCol value then be passed into the Insert statement for the Article table? Would this be done with a "SELECT KeyCol FROM INSERTED" trigger on the Catalogue table? If so how do you trap that output in a variable to be passed to the article insert?

    All help and comments are appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not clear on the relationship between articles and catalogs. Must an article be in a catalog? Can an article be in more than one catalog?

    It would also help to know what database engine you are targeting (Db2, Oracle, MS-SQL, etc). This can influence many design choices.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Location
    Raleigh, NC, USA
    Posts
    19
    Apologies. I am using MSSQL 2000. An article can only appear in one catalog and must appear in a catalog.

    The key here is that I am calling a SP, usp_ins_Article. Passed into the SP are two values, ArticleName, and CatalogName. The stored procedure checks to see if the CatalogName exists and if not it calls usp_ins_Catalog, passing in the catalog name. Once that is complete I need to have the value of the KeyCol for the new record. This is then inserted into the table (tblArticles) as a foreign key along with the ArticleName.

    What I am hoping for is to have the Catalog SP return the KeyCol value into a variable in the Articles SP.

    I hope this makes sense, and thanks for your quick reply.
    Last edited by shaun27612; 07-22-04 at 17:30.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your insert stored procedure concocts the KeyCol value using code, just keep a copy. If it uses an IDENTITY column, recover the value using @@IDENTITY.

    In both your CREATE PROCEDURE paramenter list and your EXECUTE parameter list for the insert procedure, include the OUTPUT modifier for a parameter to return the value to the caller.

    That should get you on your way!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •