In SQL Server is it possible to get "id" of a record when Insert is executed?

Question

In SQL Server 2005 I have an "id" field in a table that has the "Is Identity" property set to 'Yes'. So, when an Insert is executed on that table the "id" gets set automatically to the next incrementing integer. Is there an easy way when the Insert is executed to get what the "id" was set to without having to do a Select statement right after the Insert?

duplicate of:
Best way to get identity of inserted row?

Answer 1

SCOPE_IDENTITY(); is your best bet. And if you are using .NET just pass an our parameter and check the value after the procedure is run.

CREATE PROCEDURE [dbo].[InsertProducts]
    @id             INT             = NULL OUT,
    @name           VARCHAR(150)    = NULL,
    @desc           VARCHAR(250)    = NULL

AS

    INSERT INTO dbo.Products
       (Name,
        Description)
    VALUES
       (@name,
        @desc)

    SET @id = SCOPE_IDENTITY();

Answer 2

If you're inserting multiple rows, the use of the OUTPUT and INSERTED.columnname clause on the insert statement is a simple way of getting all the ids into a temp table.

DECLARE @MyTableVar table( ID int,  
                               Name varchar(50),  
                               ModifiedDate datetime);  
INSERT MyTable  
        OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar  
SELECT someName, GetDate() from SomeTable