I'm having no end of hassle trying to get a value returned from a SQL Server 2000 stored procedure. I'm writing a generic SQL/Stored Procedure execution routine and everything else works but this.

For test purposes, the procedure just returns the value 1. When I try to get the value back using parameter 0, the value is empty.

This is the code:

' Execute a query or Stored Procedure
'
Public Function doQuery(eQueryType As enumQueryType, _
sCommandText As String, _
Optional colParams As Collection = Nothing) As Variant

Dim Cmd As ADODB.Command
Dim recResults As New ADODB.Recordset
Dim i As Integer

' Associate this command with the current database connection
'
Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = mConn

With Cmd

' Set up the ADO Command
'
Cmd.CommandText = sCommandText

If eQueryType = ProcSingleResult Then

.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ReturnValue", adInteger, adParamReturnValue)

End If

End With
' Set up any parameters, if we have any
'
If Not (colParams Is Nothing) Then

For i = 1 To colParams.Count
Cmd.Parameters.Append colParams(i)
Next i

End If

Set recResults = Cmd.Execute

Select Case eQueryType

Case QueryRecordSet, ProcRecordSet:
Set doQuery = recResults

Case ProcSingleResult:
doQuery = Cmd.Parameters(0).Value

Case ProcNoResult, QueryNoResult

End Select


End Function

Its not yet finished, but works ok with recordsets from queries or stored procedures.

Any ideas?