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