How to get the return value from a stored procedure executed with sp_executesql

Question

I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get the return value from the procedure.

This is a simplified version of what I've got:

DECLARE @errorLogId INT
DECLARE @otherDbName NVARCHAR(MAX) = 'Foo' -- Get the database name from somewhere
DECLARE @sql NVARCHAR(MAX)

SET @sql = @otherDbName + '.dbo.SomeProc'

-- I want @errorLogId to be the return value from SomeProc, this didn't work.    
EXECUTE @errorLogId = sp_executesql @sql

IF @errorLogId <> 0
   RAISERROR('SomeProc failed, ErrorLogId = %d.', 16, 1, @errorLogId) WITH SETERROR

If an error occurs in SomeProc it is caught and an entry is written to an error table (in the other database), then SomeProc returns the error table ID (identity) of the record that was written. Otherwise SomeProc returns 0.

I know SomeProc is failing because an error is written to the error log in the other database, but in the local database @errorLogId is 0 which is treated as success. I thought the return value would be 'passed through' sp_executesql, but I think the return value is actually from sp_executesql not SomeProc (i.e. sp_executesql can be successful regardless of whether SomeProc succeeded or failed).

Is there a way to access the return value from @otherDbName.dbo.SomeProc in the local database?

Thanks

Answer 1

You need to use the OUTPUT parameter, as it is described in the MSDN article:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS
RETURN 2

This code will give you the result:

declare @ret int

exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT 
select @ret as result


result
-----------
2

(1 row(s) affected)

Answer 2

• Create Proc with 2 regular parameters and 1 table value type parameter and that returns an int.
• use sp_Executesql to allow for dynamically generated execution statements.

DROP PROC IF EXISTS tmp.EraseMeIfYouCan
DROP TYPE IF EXISTS tmp.EraseMeTo
GO

CREATE TYPE tmp.EraseMeTo AS TABLE(Id INT)
GO
CREATE PROC tmp.EraseMeIfYouCan
    @Debug TINYINT ,                                -- 0 No debug, 1 debug + print, 2 = print only
    @P2 AS tmp.EraseMeTo READONLY,
    @DebugRetVal INT = 0                            
AS
SELECT *
FROM   @P2

    RETURN @DebugRetVal
GO
SET NOCOUNT ON
DECLARE @P INT = 1
DECLARE @I INT = 0
DECLARE @R INT
DECLARE @EM2 AS tmp.EraseMeTo

WHILE @I < 10
BEGIN
    SET @I += 1
    INSERT INTO @EM2
      (
        Id
      )
    VALUES
      (
        @I
      )
END
PRINT 'Start sp-exec'
EXEC SP_ExecuteSQL N'Exec @R = tmp.EraseMeIfYouCan @Debug, @P2, @DebugRetVal',
     N'@R Int Output, @P2 As tmp.EraseMeTo ReadOnly, @Debug TinyInt, @DebugRetVal Int',
     @R = @R OUTPUT,
     @P2 = @EM2,
     @Debug = 0,
     @DebugRetVal = 3

SELECT @R

Answer 3

Execute procedure(containing Output and Input parameters) and view results

IF spCHGAssetGet is the stored-procedure name.

@CustomerID, @AssetID are the output parameters.

@IDTicketItem, @IDExtWarranty are the input parameters.

DECLARE @CustomerID NVARCHAR(100) 
DECLARE @AssetID    NVARCHAR(100) 
EXEC spCHGAssetGet
@IDTicketItem = 4764,
@IDExtWarranty = NULL,
@CustomerID = @CustomerID OUT,
@AssetID = @AssetID OUT
SELECT @CustomerID, @AssetID