August 25, 2011 at 9:00 am
Since Scope_Identity() is specific to scope, if a stored procedure were to call another stored procedure which does an INSERT on a table with an identity column, could the parent stored procedure use Scope_Identity() to obtain the identity of the newly inserted record? I'm thinking yes since the sp that did the insert would be within the scope of the parent sp.
August 25, 2011 at 9:19 am
No. Easily tested, scope_identity() will not return the identity of another procedure in the same session:
CREATE TABLE test(col1 INT IDENTITY(1,1))
GO
CREATE PROCEDURE proc_1 AS
INSERT INTO test DEFAULT VALUES
GO
CREATE PROCEDURE PROC_2 AS
EXEC proc_1
DECLARE @return INT=SCOPE_IDENTITY()
SELECT @return
GO
EXEC proc_2
@@IDENTITY will, but I avoid using it as it could be affected by triggers etc. If I had to do this, I'd use SCOPE_IDENTITY() or the OUTPUT clause on the inserting procedure and then pass this to the calling procedure as an output parameter
August 25, 2011 at 9:20 am
Scope Identity Returns Newly Inserted Identity Within the Current Scope : Current Scope Can Be A "Stored Procedure, Trigger, UDF"
If You Have A Nested Stored Procedure Then That Comes Under Different Scope.
The Case Described Above Will Return Identity Value Generated Inside Parent Stored Procedure.
For More..
http://mssqlguide.kuldeepbisht.com/2011/06/difference-between-identcurrent.html
Thanks
Kuldeep Bisht
Kuldeep Bisht
Simplion Technologies
http://mssqlguide.kuldeepbisht.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply