CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
user2462054
user2462054

Reputation: 53

Capture a value output by a stored procedure in the calling T-SQL script

I'm building unit tests in my database project. I have a stored procedure that I'm building tests for that is used in a script task in an SSIS package. The result set is mapped to one of the package variables.

I'm not sure how I can find that variable result in my unit test script?

This is the stored procedure. It's the AppInstanceID I'd like to capture:

Create Procedure [log].LogStartOfApplication
    @ApplicationName varchar(255)
As
 declare @ErrMsg varchar(255)
 declare @AppID int = (Select ApplicationID From cfg.Applications Where ApplicationName = @ApplicationName)

If (@AppID Is Null)
    begin
        set @ErrMsg = 'Cannot find ApplicationName ' + Coalesce(@ApplicationName, '<NULL>')
        raiserror(@ErrMsg,16,1)
        return-1
    end

Insert Into [log].SSISAppInstance
    (ApplicationID, StartDateTime, Status)
    Output inserted.AppInstanceID
    Values
    (@AppID, GetDate(), 'Running')

And this is my unit test script:

-- database unit test for log.LogStartOfApplication
DECLARE @RC AS INT, @ApplicationName AS VARCHAR (255);

SELECT @RC = 0,
       @ApplicationName = 'Unit Test Application';

EXECUTE @RC = [log].[LogStartOfApplication] @ApplicationName;

SELECT @RC AS RC;

I thought that the value would be returned into the @RC variable but of course it isn't. Is there a way to capture the result set in my @RC variable?

Upvotes: 1

Views: 1212

Answers (1)

TT.
TT.

Reputation: 16144

Use a stored procedure with an OUTPUT parameter

Upvotes: 1

Related Questions