How to get SCOPE_IDENTITY () from INSERT in an EXEC () statement

Question

I am creating a dynamic insert statement in a stored procedure.

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

SQL insert works fine, but after doing SET @Record_ID = Scope_Identity() I don't get the value.

How can I fix this? Do I need to wrap it in EXEC ?

Answer 1

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 

Answer 2

You can also try this.

 CREATE PROCEDURE [dbo].[SaveSingleColumnValueFromGrid] ( @TableName VARCHAR(200), @ColumnName VARCHAR (200), @CompareField VARCHAR(200), @CompareValue VARCHAR(200), @NewValue VARCHAR(200), @Result INT OUTPUT ) AS BEGIN DECLARE @SqlString NVARCHAR(2000), @id INTEGER = 0; IF @CompareValue = '' BEGIN SET @SqlString = 'INSERT INTO ' + @TableName + ' ( ' + @ColumnName + ' ) VALUES ( ''' + @NewValue + ''' ) ; SELECT @id = SCOPE_IDENTITY()'; EXECUTE sp_executesql @SqlString, N'@id INTEGER OUTPUT', @id OUTPUT END ELSE BEGIN SET @SqlString = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = ''' + @NewValue + ''' WHERE ' + @CompareField + ' = ''' + @CompareValue + ''''; EXECUTE sp_executesql @SqlString set @id = @@ROWCOUNT END SELECT @Result = @id END 

Answer 3

Yes, it should be part of dynamic sql, which is scope_identity () for