Link to home
Start Free TrialLog in
Avatar of phisis
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
ASKER CERTIFIED SOLUTION
Avatar of adathelad
adathelad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of phisis
phisis

ASKER

Thanks... that worked great.
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  
Avatar of phisis

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  
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.