CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
TheVillageIdiot
TheVillageIdiot

Reputation: 40497

SCOPE_IDENTITY - procedure calling another procedure to make insert

I have a stored procedure (let's say sp1) which executes some code to check if passed parameter does not violate business rules. After validation it calls another stored procedure (say sp2) which actually inserts the record in a table.

My question is whether I can use SCOPE_IDENTITY in sp1 to get the IDENTITY inserted in the table by sp2?

Upvotes: 1

Views: 1427

Answers (4)

David Hall
David Hall

Reputation: 33143

As Joakim says, SCOPE_IDENTITY() will not return the identity from the second procedure. From the MSDN docs on SCOPE_IDENTITY()

A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

@@Identity does behave to way you want but has the major downside that it picks up the latest identity inside the current connection including triggers. Even if you are 100% certain that there are no triggers acting on the relevant tables, anyone adding a trigger later on will break this, introducing a tricky to find bug so given how simple using an output parameter is, I'd really advise against @@Identity.

Upvotes: 3

HLGEM
HLGEM

Reputation: 96552

You create an output parameter to hold the identity value you want in the proc doing the insert. And podpulate it with scope_identitiy or the results of an output clause.

Then you create a variable of the same name in the calling proc and call the proc this way:

exec @id = usp_my_proc @someinputvariable, @someotherinputvariable

Upvotes: 1

Tim
Tim

Reputation: 5421

You should always strive to encapsulate whenever possible. Not only can SP2 return the new scope identity value; SP2 should return the new scope identity value. It can be returned as a RETURN value or as an OUT parameter.

Always best to avoid globals.

Upvotes: 0

Joakim
Joakim

Reputation: 2217

SCOPE_IDENTITY() provides the latest id from a scope, so as soon as the sql hits a "GO" a new scope is created and you wouldn't receive the identity, you could add an output parameter on sp2 to receive the identity which is probably the best way.

Upvotes: 4

Related Questions