Here's how I open a record set that uses a stored proc

proc code :

CREATE PROCEDURE [dbo].[SPNDemo] @ObjectName as varchar(50), @id as int output

AS

SET NOCOUNT ON

SET @id = object_id(@ObjectName)

SELECT

name

, XType

FROM dbo.SysObjects

WHERE name like '%' + @ObjectName + '%'

ORDER BY XType, Name

IF @@Rowcount > 0

BEGIN

RETURN 0

END

ELSE

BEGIN

RETURN 1

END

SET NOCOUNT OFF

GO

test the proc from query analyser :

Declare @id as int

Declare @return as int

exec @Return = documentation.dbo.SPNDemo 'SysObjects', @id output

--returns only 1 line

select @id as 'id is found', @return as success

exec @Return = documentation.dbo.SPNDemo 'Sys', @id

--returns a full recordset

select @id as 'no match for id', @return as success

exec @Return = documentation.dbo.SPNDemo 'This object doesn''t exists', @id

--returns a nothing

select @id as 'no match for id', @return as failed

function that returns the recordset :

Private Function exec_SPNDemo(ByVal ObjectName As String, ByRef id As Integer, Optional ByRef ReturnValue As Integer) As ADODB.Recordset

On Error GoTo Gestion

Dim MyCmd As ADODB.Command

Set MyCmd = New ADODB.Command

MyCmd.CommandText = "dbo.SPNDemo"

MyCmd.CommandType = adCmdStoredProc

Dim MyParam As ADODB.Parameter

Set MyParam = New ADODB.Parameter

MyParam.Direction = adParamReturnValue

MyParam.Name = "@Return"

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@ObjectName"

MyParam.Value = ObjectName

MyParam.Size = 50

MyParam.Direction = adParamInput

MyParam.Type = adVarChar

MyCmd.Parameters.Append MyParam

Set MyParam = New ADODB.Parameter

MyParam.Name = "@id"

MyParam.Value = id

MyParam.Size = 4

MyParam.Direction = adParamInputOutput

MyParam.Type = adInteger

MyCmd.Parameters.Append MyParam

Dim MyRs As ADODB.Recordset

Set MyRs = New ADODB.Recordset

MyRs.CursorLocation = adUseClient

MyCn.Open

MyCmd.ActiveConnection = MyCn

MyRs.Open MyCmd, , adOpenKeyset, adLockOptimistic

If MyRs.State = 1 Then

Set exec_SPNDemo = MyRs.Clone

exec_SPNDemo.ActiveConnection = Nothing

Else

Set exec_SPNDemo = Nothing

End If

MyCn.Close

ReturnValue = CInt(MyCmd.Parameters("@Return").Value)

id = MyCmd.Parameters("@id").Value

DisposeRS MyRs

Set MyParam = Nothing

Set MyCmd = Nothing

Exit Function

Gestion:

ErrHandler ModuleName, Me.Name, "exec_SPNDemo", Err

MsgBox Err.Description & " : " & Err.Number

End Function