Returning a value from a stored procedure

问题:

I have a main procedure, and in this SP it does the following piece of code:

EXEC @PropertyID = [import].[spInsertIntoTblMemberProperties]
blah blah blah

And then in my spInsertIntoTblMemberProperties the following two lines of code:

select SCOPE_IDENTITY() as 'MemberPropertyID'; (if it is a new property)
select MemberPropertyID as 'MemberPropertyID'; (if it is an existing property)
from blah blah blah

However, this doesn't make @PropertyID equal the 'MemberPropertID' and I was wondering how I would do so.

Thanks, Dan

回答1:

You'd need RETURN to do it yourway
This is not ideal though: this should be reserved for status.

I'd use an OUTPUT parameter

CREATE PROC ... 
    @PropertyID int OUTPUT
AS
...

select @PropertyID = SCOPE_IDENTITY() as 'MemberPropertyID'; (if it is a new property)
...
select @PropertyID = MemberPropertyID as 'MemberPropertyID'; (if it is an existing property)
from blah blah blah
...
GO

EXEC [import].[spInsertIntoTblMemberProperties] @PropertyID OUTPUT

Example of differences in technique: set @var = exec stored_procedure