patternsqlMinor
return scope_identity() question
Viewed 0 times
returnscope_identityquestion
Problem
environment sql server 2005
In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the original stored proc. I am doing this by using
In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the original stored proc. I am doing this by using
return scope_identity(); My understanding is return is only used for error messages/status codes. Is this a "good" way of doing what I need to do, or is there another "better" way?Solution
All procedures have an integer return value. Your method would work, there is no hard and fast rule against it.
Alternatively, you could return an output parameter.
Example:
create procedure YourProcedure
@ID output
as
insert into YourTable (bunch of values)
select @ID = scope_identity()
go
You then call your procedure as follows:
declare @TheID int
exec YourProcedure @TheID output
select @TheID
Alternatively, you could return an output parameter.
Example:
create procedure YourProcedure
@ID output
as
insert into YourTable (bunch of values)
select @ID = scope_identity()
go
You then call your procedure as follows:
declare @TheID int
exec YourProcedure @TheID output
select @TheID
Context
StackExchange Database Administrators Q#2405, answer score: 5
Revisions (0)
No revisions yet.