Início / dba / Perguntas / 97670
Accepted
WileCau
Asked: 2015-04-13 18:29:38 +0800 CST

Como obter o valor de retorno de um procedimento armazenado executado com sp_executesql

  • 772

Preciso chamar um procedimento armazenado em outro banco de dados e verificar o valor de retorno. O nome do outro banco de dados pode variar, então estou tentando usar sp_executesql, mas não consegui descobrir como obter o valor de retorno do procedimento.

Esta é uma versão simplificada do que eu tenho:

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

Se ocorrer um erro em SomeProc, ele for detectado e uma entrada for gravada em uma tabela de erros (no outro banco de dados), SomeProc retornará o ID da tabela de erros (identidade) do registro que foi gravado. Caso contrário, SomeProc retornará 0.

Eu sei que SomeProc está falhando porque um erro é gravado no log de erros no outro banco de dados, mas no banco de dados local @errorLogId é 0, que é tratado como sucesso. Eu pensei que o valor de retorno seria 'passado' sp_executesql, mas acho que o valor de retorno é realmente de sp_executesql não SomeProc (ou seja, sp_executesql pode ser bem-sucedido independentemente de SomeProc ter sucesso ou falhado).

Existe uma maneira de acessar o valor de retorno de @otherDbName.dbo.SomeProc no banco de dados local?

Obrigado

sql-server sql-server-2008-r2

3 respostas

  1. Best Answer

    Você precisa usar o parâmetro OUTPUT, conforme descrito no artigo do MSDN :

    Dado este procedimento armazenado fictício:

    CREATE PROCEDURE sp_test AS
RETURN 2

    Este código lhe dará o resultado:

    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)
    • 5
    • Crie Proc com 2 parâmetros regulares e 1 parâmetro de tipo de valor de tabela e que retorne um int.
    • use sp_Executesql para permitir instruções de execução geradas dinamicamente.
    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
    • 0

  3. Execute o procedimento (contendo os parâmetros de saída e entrada) e visualize os resultados

    IF spCHGAssetGet é o nome do procedimento armazenado.

    @CustomerID, @AssetID são os parâmetros de saída.

    @IDTicketItem, @IDExtWarranty são os parâmetros de entrada.

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

relate perguntas