Need to perform a DB Insert then return an ID?

I need to write a Stored Procedure that will Insert a record into a Table and then return the Primary Key and I'm not sure how to do that. I'm using SQL Server 2008.
BlakeMcKennaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
CREATE PROC sp_foo AS

INSERT INTO Customer(fk_customer_type_id, name)
VALUES (1, 'Jack Wagon')

-- Return the identity field generated by the above insert
SELECT SCOPE_IDENTITY()

GO

Open in new window

0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
if your table has an identity column and is the PK ,  perform the insert first and use SCOPE_IDENTITY() to get the id

INSERT into ...
SELECT SCOPE_IDENTITY()
0
 
BlakeMcKennaAuthor Commented:
Thanks guys...that worked!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, I'll bite.  How did my comment not correctly answer the question?
0
 
BlakeMcKennaAuthor Commented:
Jim,

Your answer was correct. Since there were 2 correct answers, I accepted multiple answers.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm showing that only the second answer was accepted, so if your intent was to split you'll need to re-open the question and re-award points.
0
 
BlakeMcKennaAuthor Commented:
How do I reopen a ?
0
 
BlakeMcKennaAuthor Commented:
Sorry...I'll redo it!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
 
BlakeMcKennaAuthor Commented:
Your welcome...and thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.