调存储过程的问题

[fj182]

我写了个函数来调用存储过程，函数如下
Public Function ExecProc(ByVal ProcName As String, ByVal Para As Variant) As Recordset

Dim i As Integer
Dim comNew As Command
Dim prmNew As Parameter
Dim rs As Recordset
Dim conNew As Connection

Set conNew = OpenConnection() '打开连接

If UBound(Para) = 0 Then
Set rs = conNew.Execute(ProcName, , adCmdStoredProc)
Else
Set comNew = New Command

'找出存储过程需要什么参数
With comNew
.ActiveConnection = conNew
.CommandText = ProcName
.CommandType = adCmdStoredProc
.Parameters.Refresh
End With

'将输入参数每项的值赋给Parameters集合并执行存储过程
i = 1
For Each prmNew In comNew.Parameters
If prmNew.Direction = adParamInput Or prmNew.Direction = adParamInputOutput Then
prmNew.Value = Para(i)
i = i + 1
End If
Next prmNew
Set rs = comNew.Execute()
End If
Set ExecProc = rs
End Function
其中，ProcName为存储过程名，Para是一个参数数组，调用时

Dim v As Variant
Dim rst As Recordset

ReDim v(2)

v(1) = 123
v(2) = "abc"

Set rst = ExecProc("Test", v)
程序运行时也没报错，可就是返回的记录集为空，那位大哥帮我看看。

[fj182]

补充一下，存储过程没问题。

[faysky2]

For Each prmNew In comNew.Parameters
If prmNew.Direction = adParamInput Or prmNew.Direction = adParamInputOutput Then
prmNew.Value = Para(i)
i = i + 1
End If
Next prmNew
---------------------------------
试试这样：

Dim param As New ADODB.Parameter
For Each prmNew In comNew.Parameters
If prmNew.Direction = adParamInput Or prmNew.Direction = adParamInputOutput Then
Set param = comNew.CreateParameter(prmNew.Name, prmNew.Type, adParamInput, prmNew.Size, Para(i))
comNew.Parameters.Append param
i = i + 1
End If
Next prmNew

[fj182]

还是不行啊

[fj182]

存储过程执行后返回的Recordset是关闭的，但我从查询分析器里能够返回结果集。

[fj182]

问题解决了，加了句set nocount on,靠，瞎忙活了一下午。