vba 通过 ADO 检索存储过程输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19928509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving stored procedure output parameter via ADO
提问by ecasper
I'm stuck with this for more than 48 Hrs now. I'm trying to retrieve a output parameter value using ADO objects. here is my vba code.
我现在已经坚持了超过 48 小时。我正在尝试使用 ADO 对象检索输出参数值。这是我的 vba 代码。
Function ExecuteCommand(cSp As String, aParameters As Variant) As Integer
Dim lnReturn As Integer
Dim loCommand As ADODB.Command
Dim prm As ADODB.Parameter
Dim rds As ADODB.Recordset
Set loCommand = New ADODB.Command
loCommand.CommandText = cSp
loCommand.CommandType = adCmdStoredProc
Set loCommand.ActiveConnection = goConnection
loCommand.NamedParameters = True
Dim iCount As Integer
For iCount = LBound(aParameters) To UBound(aParameters)
If aParameters(iCount, 0) <> "" Then
If aParameters(iCount, 1) = adNumeric Then
Set prm = loCommand.CreateParameter(aParameters(iCount, 0), aParameters(iCount, 1), adParamInput)
prm.Precision = 18
prm.NumericScale = 0
prm.Value = aParameters(iCount, 3)
Else
Set prm = loCommand.CreateParameter(aParameters(iCount, 0), aParameters(iCount, 1), adParamInput, , aParameters(iCount, 3))
End If
loCommand.Parameters.Append prm
End If
Next
Set prm = loCommand.CreateParameter("@returnval", adInteger, adParamOutput, , lnReturn)
loCommand.Parameters.Append prm
Set rds = loCommand.Execute
' Check the value of lnReturn and it's zero here.
lnReturn = loCommand.Parameters("@returnval").Value
ExecuteCommand = lnReturn
End Function
My stored procedure as follows...
我的存储过程如下...
USE [table_name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spname]
@para1 int,
@para2 int,
@para3 int,
@returnval int Output
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM type_table WHERE type1=@para1 and type2=@para2 and type3=@para3
SET @returnval = @@ROWCOUNT
RETURN @returnval
END
These are my findings
这些是我的发现
lnReturn = 0 always
rds.EOF = False
rds.RecordCount = -1
My question is I'm not getting the correct affected row count which is 1. I'm not sure what is wrong with the code and can someone give me a hand?
我的问题是我没有得到正确的受影响行数，即 1。我不确定代码有什么问题，有人可以帮我吗？
采纳答案by Joe
This is probably because, as Technet says, In the case of output parameters and return values, the values are not returned until the data of the Recordset object has been fetched completely or the Recordset has been closed.
这可能是因为，正如 Technet 所说，在输出参数和返回值的情况下，直到 Recordset 对象的数据已完全获取或 Recordset 已关闭时，才会返回值。
Either close your RecordSet (rds.Close), if you're not interested in the data returned by the SELECT statement, or loop through it until you've read all rows.
rds.Close如果您对 SELECT 语句返回的数据不感兴趣，请关闭您的 RecordSet ( )，或者循环遍历它直到读取所有行。
As an aside, if you are never interested in the data returned by the select statement, only the row count, then it's more efficient not to return it. So if you can modify your SP, you could change it to:
顺便说一句，如果您从不对 select 语句返回的数据感兴趣，只对行数感兴趣，那么不返回它会更有效。因此，如果您可以修改 SP，则可以将其更改为：
SELECT @returnval = COUNT(*) FROM type_table WHERE type1=@para1 and type2=@para2 and type3=@para3
回答by Tobin
If you are not expecting a recordset as part of your stored procedure call, use cmd1->Execute(NULL, NULL, ADO::adExecuteNoRecords);
如果您不希望将记录集作为存储过程调用的一部分，请使用 cmd1->Execute(NULL, NULL, ADO::adExecuteNoRecords);
回答by Nithesh Narayanan
Have a try after removing SET NOCOUNT ON;from your procedure
SET NOCOUNT ON;从您的程序中删除后尝试
I think this will help you. The affected row count will not returned if you set SET NOCOUNT ON;in procedure.
我想这会帮助你。如果您SET NOCOUNT ON;在程序中设置，则不会返回受影响的行数。
