SQL: How to get the id of values I just INSERTed?

Solution 1:

@@IDENTITY is not scope safe and will get you back the id from another table if you have an insert trigger on the original table, always use SCOPE_IDENTITY()

Solution 2:

This is how I do my store procedures for MSSQL with an autogenerated ID.

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();

SQL: How to get the id of values I just INSERTed?

Solution 1:

Solution 2:

Related

Recent Posts