How to obtain SCOPE_IDENTITY() from INSERT run in EXEC() statement

sql sql-server scope-identity
Jon Leach picture Jon Leach · Sep 6, 2012 · Viewed 23.5k times · Source

I am building a dynamic insert statement within a stored procedure.

I build up the sql syntax in a variable and then execute it with EXEC(@VarcharVariable).

The SQL insert works fine but when I execute SET @Record_ID = Scope_Identity() afterwards, I don't get a value.

How can I capture this? Do I need to wrap it into the EXEC?

Answer

AdaTheDev picture AdaTheDev · Sep 6, 2012

Basic example, using sp_executesql

DECLARE @sql NVARCHAR(MAX)
DECLARE @Id INTEGER

SET @sql = 'INSERT MyTable (Field1) VALUES (123); SELECT @Id = SCOPE_IDENTITY()'
EXECUTE sp_executesql @sql, N'@Id INTEGER OUTPUT', @Id OUTPUT

-- @Id now has the ID in

Related questions

SCOPE_IDENTITY() for GUIDs?

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More