Get Key ID in Stored Procedure after Insert

Posted on 2005-05-04
Last Modified: 2008-02-26
If I have a database with a key field, and I want to insert a new row such as
Insert into Customer(Name, houseNum)
values(@name, @housenum)
then I want to get the key for that new row, what would be the best method for getting it?  Can I do it in the same stored procedure or do I have to make a new one?

Thank You!
Question by:m_evergreen
    LVL 2

    Expert Comment

    Is the key an identity field?


    LVL 28

    Accepted Solution

    If you have an identity field in your Customer table, you can use SCOPE_IDENTITY() to get the key assigned to the newly inserted record.  As an example:


    Insert into Customer(Name, houseNum)
    values(@name, @housenum)


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now