There are several ways to capture the output from a stored procedure in SQL Server, and if you are working with an existing code set chances are you will not be able to only rely on one of the methods. From least to most flexibility, the 3 methods for passing data back from a stored procedure:

• the return code is a single integer value that can be set to infer meaning
• OUTPUT parameters can be used to pass one to many variables into a stored procedure to be updated with new values
• SELECT statements in a stored procedure to create results sets with one to many columns and one to many rows

Return Codes

The return code is a light-code way to pass any whole-number (integer) back from a stored procedure. By adding RETURN before the value at the end of your procedure, the value can be captured with the procedure execution.

CREATE PROCEDURE RTNS_RETURNCODE
    @APARAMATER NVARCHAR(10)
AS
BEGIN
    UPDATE dbo.Badges
    SET NAME = @APARAMATER
    WHERE ID = 100

    RETURN @@ROWCOUNT
END
GO

-- use of the return code
DECLARE @UPDATEDBADGES INT
EXEC @UPDATEDBADGES = RTNS_RETURNCODE N'BESTBADGEEVER'

SELECT @UPDATEDBADGES

Output Parameters

One step beyond the return code is the ability to pass in parameter(s) to a stored procedure that after execution contain the new value as set by the stored procedure. In creating the stored procedure, one or multiple parameters are modified with the OUTPUT keyword and updated within the stored procedure.

CREATE PROCEDURE RTNS_MULTIPLEOUTPUT
    @INPUTONE INT 
    , @OUTPUTTWO INT OUTPUT
    , @OUTPUTTHREE NVARCHAR(50) OUTPUT
AS
BEGIN

    SET @OUTPUTTWO = 2 + @INPUTONE
    SET @OUTPUTTHREE = 'OUTPUT OF A STRING'

END

When calling the stored procedure, the OUTPUT keyword needs to be specified with the variables where we are looking to receive an updated value base.

The values of the variables after the stored procedure is executed reflect the operations of the stored procedure.

DECLARE @INPUTONE INT = 1
DECLARE @INPUTTWO INT 
DECLARE @INPUTTHREE NVARCHAR(50)

EXEC RTNS_MULTIPLEOUTPUT @INPUTONE, @INPUTTWO OUTPUT, @INPUTTHREE OUTPUT

SELECT @INPUTTWO, @INPUTTHREE

Results Sets

In concluding a stored procedure with a SELECT statement, you open yourself up to a whole subset of ways to capture data output from a stored procedure.

CREATE PROCEDURE RTNS_RESULTSSET
    @INPUTONE INT
AS
BEGIN
    SELECT ID, CreationDate, TEXT
    FROM dbo.Comments
    WHERE PostId = @INPUTONE
END

When executing the stored procedure as a part of an INSERT statement, you can capture the SELECT results from the stored procedure. This can be an INSERT into a table variable, temporary table, or non-temporal table. The example below creates a table variable, then uses it to store the output from RTNS_RESULTSSET.

DECLARE @INPUTONE INT = 47497
DECLARE @CAPTURETABLE TABLE(ID INT, CREATIONDATE DATETIME, COMMENTTEXT NVARCHAR(700))

INSERT INTO @CAPTURETABLE (ID, CREATIONDATE, COMMENTTEXT)
    EXEC RTNS_RESULTSSET @INPUTONE

SELECT * FROM @CAPTURETABLE

It is prudent to note that for use in applications, multiple results sets will be returned to the application by nested stored procedures with uncaptured SELECT statements.

More Information

https://social.msdn.microsoft.com/Forums/en-US/0867bfbe-550a-46b7-b2fa-b27ee2677ea1/capture-the-output-of-a-stored-procedure-in-a-calling-script
https://www.wiseowl.co.uk/blog/s365/returning-values-from-stored-procedures.htm
https://sqlservercode.blogspot.com/2006/09/five-ways-to-return-values-from-stored.html