将存储过程返回值分配给 VBA 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17868484/
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
Assign a stored procedure return value to a VBA variable
提问by philthyfool
This should be real easy, but I haven't found a real concise answer yet. I have a very simple stored procedure in sql server that returns an integer value. All I want to do is get that return value into a variable for use in Access.
这应该很容易，但我还没有找到一个真正简洁的答案。我在 sql server 中有一个非常简单的存储过程，它返回一个整数值。我想要做的就是将该返回值放入一个变量中，以便在 Access 中使用。
Stored Procedure:
存储过程：
ALTER PROCEDURE [dbo].[out_GetNextID]
@NextSumID integer
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @NextSumID = IDENT_CURRENT('Outage Summary')+IDENT_INCR('Outage Summary')
RETURN @NextSumID
END
I am using ADODB to execute the stored procedure, and I feel dumb for having to ask this, but how do I access the return value in Access after I run cmd.Execute? Thanks in advance and sorry for the lame question.
我正在使用 ADODB 来执行存储过程，我觉得不得不问这个问题很愚蠢，但是我在运行 cmd.Execute 后如何访问 Access 中的返回值？提前致谢，并对这个蹩脚的问题表示抱歉。
采纳答案by Andy G
There are two approaches, using output parametersor setting a ReturnValue(discussed below). For OUTPUT parameters, quoting from this SO link:
有两种方法，使用输出参数或设置 a ReturnValue（下面讨论）。对于 OUTPUT 参数，引用此SO 链接：
essentially you just need to create a SqlParameter, set the Direction to Output, and add it to the SqlCommand's Parameters collection. Then execute the stored procedure and get the value of the parameter.
本质上，您只需要创建一个 SqlParameter，将 Direction 设置为 Output，并将其添加到 SqlCommand 的 Parameters 集合中。然后执行存储过程并获取参数的值。
See the code from that page.
请参阅该页面的代码。
However, you also need to include the word OUT (or OUTPUT) in your variable declaration:
但是，您还需要在变量声明中包含单词 OUT（或 OUTPUT）：
@NextSumID integer OUT
When you declare the variable as OUT (or OUTPUT) it will be returned automatically, with whatever value it has when the procedure finishes, so you could just use RETURN.
当您将变量声明为 OUT（或 OUTPUT）时，它将自动返回，并在过程完成时使用它具有的任何值，因此您可以只使用 RETURN。
Return Data from a Stored Procedure :MSDN
You can, instead, use RETURN @NextSumIDbecause you are just returning a single, integer, value. For this approach, you need to specify the parameter as the ReturnValue:
相反，您可以使用，RETURN @NextSumID因为您只返回单个整数值。对于这种方法，您需要将参数指定为ReturnValue：
theParameter.Direction = ParameterDirection.ReturnValue
This approach is discussed further here (MSDN).
此处 (MSDN)进一步讨论了这种方法。
回答by D_Bester
Here's how you would get the return value using a stored procedure that returns a value. You'll need reference to Microsoft ActiveX Data Objects 2.8 Library.
以下是使用返回值的存储过程获取返回值的方法。您需要参考Microsoft ActiveX Data Objects 2.8 Library。
Sub CheckValue(ByVal NextSumID As Long)
'open connnection
Dim ACon As New Connection
ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
"Initial Catalog=<Table>;Integrated Security=SSPI")
'set command
Dim ACmd As New Command
Set ACmd.ActiveConnection = ACon
ACmd.CommandText = "out_GetNextID"
ACmd.CommandType = adCmdStoredProc
'Return value must be first parameter else you'll get error from too many parameters
'Procedure or function "Name" has too many arguments specified.
ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
ACmd.Parameters.Append ACmd.CreateParameter("NextSumID", adVarChar, adParamInput, 10, NextSumID)
'execute query
Call ACmd.Execute
'get return value
Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
ACon.Close
End Sub
