Return value from stored procedure is "nothing"

[MLarsB]

I have a stored procedure running in SQL Server 2000 which returns a value
when I run it in SQL Server Management Studio 2008 (SMS). I have also tested
it in SQL Server 2000 Query Analyzer with the same result. However, when I
run it with VB.Net 2010 I get "nothing" as the value of the return parameter:
cmd.Parameters( "@ReturnCount") .Value.
How do I get a value back? The parameter I am testing on is @ReturnCount.
I will need it to work for the return code also - @ReturnCode.

I have searched the web and found quite a few examples and tried them, but
they do not work with this code.

(I have simplified the procedure for testing. I am using dynamic SQL
because the SELECT in the actual procedure uses a parameter on the FROM
clause. I use sp_executesql in order to get a return value from the
dynamic SQL.)

/*
Test procedure:

declare @ReturnCount int
declare @ReturnCode int
exec dbo.spSampGroupPRptgS 'mltpsdb02',
                           'prodreporting',
                           '''100000015''',
                           @ReturnCode output
*/

if exists (select * 
  from dbo.sysobjects 
  where id = object_id(N'dbo.spSampGroupPRptgS') and 
             OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure dbo.spSampGroupPRptgS
GO

CREATE PROCEDURE dbo.spSampGroupPRptgS
  ( @PRptgDBServer         varchar(30),
    @PRptgDatabase         varchar(30),
    @GroupParentIDs        varchar(4000),
    @ReturnCode            int output
	)
AS 
SELECT @ReturnCode = 0
DECLARE @SQL nvarchar(4000)
DECLARE @ReturnCount int

SELECT @SQL=
N'SELECT @ReturnCount = Count(*)
FROM GroupTestData
WHERE Parent_Group_Id in ('+@GroupParentIDs+')
'

DECLARE @params nvarchar(500)

SET @params = 
  N'@PRptgDBServer         nvarchar(30),
    @PRptgDatabase         nvarchar(30),
    @GroupParentIDs        nvarchar(4000),
    @ReturnCount           int output'

EXEC sp_executesql @SQL,
                   @params,
                   @PRptgDBServer  = @PRptgDBServer,
                   @PRptgDatabase  = @PRptgDatabase,
                   @GroupParentIDs = @GroupParentIDs,
                   @ReturnCount    = @ReturnCount OUTPUT

SELECT @ReturnCount

GO

----------------------------------------------------------

Public Function GetGroupParents(ByRef GroupParentsArg(,) As String) As Boolean

    Dim cmd As New SqlCommand
    Dim drGroupParents As SqlDataReader
    Dim cnString As String = gConnectionString
    Dim cn As New SqlConnection(cnString)
    Dim MaxRowCount As Integer
    Dim RowCount As Integer

    GetGroupParents = True

    Try
      cmd.CommandTimeout = 1200
      cmd.CommandText = "dbo.spSampGroupPRptgS"
      cmd.CommandType = CommandType.StoredProcedure

      cmd.Parameters.Add("@PRptgDBServer", SqlDbType.VarChar, 30).Value = gPRptgDBServer
      cmd.Parameters.Add("@PRptgDatabase", SqlDbType.VarChar, 30).Value = gPRptgDatabase
      cmd.Parameters.Add("@GroupParentIDs", SqlDbType.VarChar, 4000).Value = gGroupParentIDs

      cmd.Parameters.Add("@ReturnCount", SqlDbType.Int)
      cmd.Parameters("@ReturnCount").Direction = ParameterDirection.ReturnValue
      cmd.Parameters.Add("@ReturnCode", SqlDbType.Int)
      cmd.Parameters("@ReturnCode").Direction = ParameterDirection.Output

      ' Call spSampGroupPRptgS to get Parent Group Name, Product ID's, and Group ID's
      cmd.Connection = cn
      cmd.CommandType = CommandType.StoredProcedure
      cn.Open()
      drGroupParents = cmd.ExecuteReader

      If drGroupParents.HasRows Then

        MaxRowCount = CInt(cmd.Parameters("@ReturnCount").Value)
        MessageBox.Show("MaxRowCount: " & MaxRowCount, Application.ProductName)

        ReDim GroupParentsArg(MaxRowCount, 4)

        RowCount = 0
        While drGroupParents.Read()
          GroupParentsArg(RowCount, 0) = Trim(drGroupParents.Item("ParentGroupId").ToString)
          GroupParentsArg(RowCount, 1) = Trim(drGroupParents.Item("ParentGroupName").ToString)
          GroupParentsArg(RowCount, 2) = Trim(drGroupParents.Item("ProductId").ToString)
          GroupParentsArg(RowCount, 3) = Trim(IIf(Not IsDBNull(drGroupParents.Item("GroupId")), _
                                             drGroupParents.Item("GroupId"), "").ToString)
          RowCount += 1
        End While
      Else
        If Not gSilentMode Then
          MessageBox.Show("No Group ProdReporting Data Found", _
                          Application.ProductName & " No Group ProdReporting Data Found")
        End If
        InformationHandler("No Group ProdReporting Data Found", "")
        GetGroupParents = False
        Exit Function
      End If

    Catch ex As Exception
      ErrorHandler("DataAccessModule.GetGroupParents", "", ex)
      GetGroupParents = False
    End Try

  End Function

[ck9663]

If your SP is returning the result in query analyzer window and not in your front-end, then the problem is in your front-end. Go to .Net forum and post the portion where you access the returned result set of your SP.

Good Luck!!!


~~ CK