Get OUTPUT and RETURN parameters from a stored procedure

[fabian.oberdiek]

I have a stored procedure (MS SQL Server 2008 R2) like the following example.

Using Database Connectivity I can get the OUTPUT parameters but I can't get de RECORDSET DATA and de RETURN value.

Does anybody knows how to do that?

CREATE PROCEDURE [dbo].[TS_Teste] (@T057_S_NOMEMAQUINA VARCHAR(20), @STATUS INT OUTPUT, @ERRO NVARCHAR(500) OUTPUT)

AS

BEGIN     
  DECLARE @TABLE TABLE(CODIGO INT, DESCRICAO VARCHAR(30))

  INSERT INTO @TABLE VALUES (51, 'A')     
  INSERT INTO @TABLE VALUES (52, 'B')

  INSERT INTO @TABLE VALUES (53, 'C')

  SELECT * FROM @TABLE

  SET @STATUS = 1

  SET @ERRO = 'Nenhum erro!'

  RETURN 0

END

[fabian.oberdiek]

Now I could get the RETURN, but I still can't get the RECORDSET DATA (returns empty but shouldn't be). Please anybody help me.

[fabian.oberdiek · Accepted solution]

I finaly found what was wrong... It was necessary an only aditional line in the stored procedure. It should be like that:

CREATE PROCEDURE [dbo].[TS_Teste] (@T057_S_NOMEMAQUINA VARCHAR(20), @STATUS INT OUTPUT, @ERRO NVARCHAR(500) OUTPUT)

AS

BEGIN    
  SET NOCOUNT ON;                                                                                               -- NEW LINE!!!

  DECLARE @TABLE TABLE(CODIGO INT, DESCRICAO VARCHAR(30))

  INSERT INTO @TABLE VALUES (51, 'A')    
  INSERT INTO @TABLE VALUES (52, 'B')

  INSERT INTO @TABLE VALUES (53, 'C')

  SELECT * FROM @TABLE

  SET @STATUS = 1

  SET @ERRO = 'Nenhum erro!'

  RETURN 0

END