Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Odd problem with "Autoincrement" fields

    Hey guys,

    I have 2 tables:
    1. CommandCode( id AutoIncrement_integer, name string )
    2. FooCode( FooID integer, CommandCodeID integer)

    I have the following stored procedure

    PROCEDURE InsertRows( CodeName VARCHAR )
    INSERT INTO CommandCode ( name ) VALUES (CodeName)

    Now right here I need to INSERT INTO the "FooCode" table using the same ID that was just added to the CommandCode table. How can I retrieve the last inserted autoincrement value for "id" in the CommandTable without doing SELECT id FROM CommandCode WHERE name = CodeName

    END PROCEDURE


    Is there an easy way to do what I described above? If possible I want it to comply with SQL standards (e.g. no MS SQL specific code)

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the SQL standard way of doing it is to use a second query to retrieve the row using the alternate key (in your case this would be the name)

    the MS SQL way is to use the SCOPE_IDENTITY function (look it up in the docs)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    look up scope_identity in BoL
    George
    Home | Blog

Posting Permissions

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