ADO returning a value from SQL Server Stored Procedure

[RobMarsh]

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?

[Cakkie]

First of all, how do you declare the parameters in your stored procedure. When declaring it, you must specify that is will need to return a value back to the calling process.
Second of all, you can return data in two ways. You can use the parameter thing you are already trying, which will need you to place the value in an out-parameter in the storedprocedure. Or you could use a recordset for that, using SELECT SomeValue As Returnvalue.
Which way is best? I don't know, I can imaging the thingy with the parameters using a bit less resources, but I can't tell exactly. The second thing makes your stored procedure easier, and makes debugging a bit easier (since it's hard to catch the value returned from a stored procedure, that would lead you to creating another stored procedure to catch the value and eventually show that, probably using SELECT afterall.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[RobMarsh]

The way I'm used to doing it is by building the
.SQL text to something like "{? = call StoredProc( ?, ? )}".

I noticed that when you add a parameter, the .SQL text builds up with each one. I thought that using this could avoid having to build up the text each time for different stored procedures.

So, do I need to declare the single returned value variable as an out-parameter?

I would prefer to use the 0 parameter method if I can.