phisis
asked on
Get Scope_Identity() Value from exec of dynamic SQL
Hi,
I'll describe the issue after the code sample below.
SET @SQL = 'INSERT INTO CMN_ApplicationInstances (' + @fieldOrder + ') SELECT ' + @fieldOrder + ' FROM CMN_ApplicationInstances'
+ ' WHERE CMN_ApplicationInstancesID = ' + cast(@cmnAppInstancesID as varchar(63))
if @mode = 'Actual' or @mode = 'Output'
BEGIN
exec (@sql)
SELECT @newParentPKvalue = SCOPE_IDENTITY()
END
It appears the Scope_Identity() will not return the value created/inserted in the exec(@SQL). Is there a way I could return that identity value from the exec. I have looked into using sp_executesql, but am not sure how I would grab the identity value using it
Thanks,
Sakar
I'll describe the issue after the code sample below.
SET @SQL = 'INSERT INTO CMN_ApplicationInstances (' + @fieldOrder + ') SELECT ' + @fieldOrder + ' FROM CMN_ApplicationInstances'
+ ' WHERE CMN_ApplicationInstancesID
if @mode = 'Actual' or @mode = 'Output'
BEGIN
exec (@sql)
SELECT @newParentPKvalue = SCOPE_IDENTITY()
END
It appears the Scope_Identity() will not return the value created/inserted in the exec(@SQL). Is there a way I could return that identity value from the exec. I have looked into using sp_executesql, but am not sure how I would grab the identity value using it
Thanks,
Sakar
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or:
SET @SQL = 'INSERT INTO CMN_ApplicationInstances (' + @fieldOrder + ') SELECT ' + @fieldOrder + ' FROM CMN_ApplicationInstances'
+ ' WHERE CMN_ApplicationInstancesID = ' + cast(@cmnAppInstancesID as varchar(63))
if @mode = 'Actual' or @mode = 'Output'
BEGIN
exec (@sql)
SELECT @newParentPKvalue =@@IDENTITY()
END
SET @SQL = 'INSERT INTO CMN_ApplicationInstances (' + @fieldOrder + ') SELECT ' + @fieldOrder + ' FROM CMN_ApplicationInstances'
+ ' WHERE CMN_ApplicationInstancesID
if @mode = 'Actual' or @mode = 'Output'
BEGIN
exec (@sql)
SELECT @newParentPKvalue =@@IDENTITY()
END
ASKER
KarinLoos,
I'm don't understand the scope/session concept completely and I'm a little confused. The SELECT @newParentPKvalue =@@IDENTITY() would give me last identity in the current session. Using your solution would I still be safe if there were other Stored Procedures running in the DB (and inserting identites) while my SP was running.
Thanks
I'm don't understand the scope/session concept completely and I'm a little confused. The SELECT @newParentPKvalue =@@IDENTITY() would give me last identity in the current session. Using your solution would I still be safe if there were other Stored Procedures running in the DB (and inserting identites) while my SP was running.
Thanks
sorry it was a typo not @@IDENTITY() but @@IDENTITY
From BOL:
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. Thus SCOPE_IDENTITY will be return the last identity value generated within your stored procedure where @@IDENTITY is not limited to the scope of the stored procedure. However they both refer to the CURRENT SESSION..
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
Hope that clears it up for you. BY the way the accepted answer from Adathelad is a good answer.
From BOL:
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. Thus SCOPE_IDENTITY will be return the last identity value generated within your stored procedure where @@IDENTITY is not limited to the scope of the stored procedure. However they both refer to the CURRENT SESSION..
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
Hope that clears it up for you. BY the way the accepted answer from Adathelad is a good answer.
ASKER