vba 如何从具有命名参数的表值函数创建 ADODB 记录集

How to create an ADODB Recordset From a Table Valued Function with Named Parameters

提问by Mark Plumpton

This works:

这有效：

Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM dbo.ftblTest(1,2,3)", CP.Connection, adOpenKeyset, adLockReadOnly

But it would be nicer to do this:

但这样做会更好：

rst.Open "SELECT * FROM dbo.ftblTest(@Param1=1,@Param2=2,@Param3=3)", CP.Connection, adOpenKeyset, adLockReadOnly

If I try the second method I get the error: "parameters were not supplied for the function ftblTest"

如果我尝试第二种方法，我会收到错误消息：“没有为函数 ftblTest 提供参数”

Is it possible to use named parameters with multi-statement table-valued functions?

是否可以将命名参数与多语句表值函数一起使用？

Edit 1: Examples Added Using Command Object

编辑 1：使用命令对象添加的示例

First the SQL

首先是SQL

create function ftblTest (@Input int)
RETURNS @Results TABLE (
  OutputField int
  )
AS
BEGIN
INSERT INTO @Results SELECT @Input
Return
End

Some Code(run from inside an Access 2003 ADP, with a connection to the correct SQL DB)

一些代码（从 Access 2003 ADP 内部运行，并连接到正确的 SQL DB）

Public Sub test()
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command

  'method 1 works
  rst.Open "SELECT * FROM dbo.ftblTest(2)", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
  Debug.Print rst.Fields(0)
  rst.Close

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdTable

    'method 2 works
    .CommandText = "dbo.ftblTest(3)"
    Set rst = cmd.Execute
    Debug.Print rst.Fields(0)

    'method 3 fails
    .CreateParameter "@Input", adInteger, adParamInput, , 4
    .CommandText = "dbo.ftblTest(@Input)"
    Set rst = cmd.Execute 'error here:-2147217900   Must declare the scalar variable "@Input".
    Debug.Print rst.Fields(0)

  End With
End Sub

How can I get the named parameters to work in method 3?

如何让命名参数在方法 3 中工作？

Edit 2: test code modified to use Parameters.Append

编辑 2：修改测试代码以使用 Parameters.Append

Public Sub test()
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim p As New ADODB.Parameter

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdTable

    'Parameter Append method fails
    p = .CreateParameter("@Input", adInteger, adParamInput, , 4)
    Debug.Print p.Name, p.Type = adInteger, p.Direction = adParamInput, p.SIZE, p.Value 'note that name not set!
    With p
      .Name = "@Input"
      .Type = adInteger
      .Direction = adParamInput
      .SIZE = 4 'this shouldn't be needed
      .Value = 4
    End With
    Debug.Print p.Name, p.Type = adInteger, p.Direction = adParamInput, p.SIZE, p.Value 'properties now set
    .Parameters.Append p
    .CommandText = "dbo.ftblTest(@Input)"
    Set rst = cmd.Execute 'error here:-2147217900   Must declare the scalar variable "@Input".
    Debug.Print rst.Fields(0)

  End With
End Sub

this still doesn't work.

这仍然不起作用。

Edit 3: I removed the @ from create parameter

编辑 3：我从 create 参数中删除了 @

as suggested and tried the CommandText 3 ways and got 3 different errors:

按照建议并尝试了 CommandText 3 种方式并得到了 3 个不同的错误：

.CommandText = "dbo.ftblTest" 

error: Parameters were not supplied for the function 'dbo.ftblTest'.

错误：未为函数“dbo.ftblTest”提供参数。

.CommandText = "dbo.ftblTest()" 

error: An insufficient number of arguments were supplied for the procedure or function dbo.ftblTest.

错误：为过程或函数 dbo.ftblTest 提供的参数数量不足。

.CommandText = "dbo.ftblTest(Input)" 

error: "Input" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

错误：“输入”不是可识别的表提示选项。如果它打算作为表值函数或 CHANGETABLE 函数的参数，请确保您的数据库兼容模式设置为 90。

回答by mrdenny

Yes, you can use parameters with a table function.

是的，您可以将参数与表函数一起使用。

rst.Open "SELECT * FROM dbo.ftblTest(@Param1,@Param2,@Param3)", CP.Connection, adOpenKeyset, adLockReadOnly

Before you open the database connection add parameters and set their values.

在打开数据库连接之前添加参数并设置它们的值。

回答by Robert L Davis

Don't use the @ in the name of your parameter and don't list the parameter by name in the command text. I've always done this with a stored procedure, so I'm not sure exactly how the paranethesis are handle for the command text.

不要在参数名称中使用 @，也不要在命令文本中按名称列出参数。我一直使用存储过程来完成此操作，因此我不确定如何处理命令文本的副词。

try:

尝试：

.CreateParameter "Input", adInteger, adParamInput, , 4

.CreateParameter "Input", adInteger, adParamInput, , 4

And:

和：

.CommandText = "dbo.ftblTest()"

.CommandText = "dbo.ftblTest()"

Or:

或者：

.CommandText = "dbo.ftblTest"

.CommandText = "dbo.ftblTest"

回答by BrackeEric

This should work:

这应该有效：

Dim cmd As New ADODB.Command

将 cmd 调暗为新的 ADODB.Command

With cmd

用 cmd

.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdTable
'you need to add question a mark for each parameter
.CommandText = "dbo.ftblTest(?)"
'you can even add a order by expression like:
.CommandText = "dbo.ftblTest(?) ORDER BY ..."
.Parameters.Append .CreateParameter("@Input", adInteger, adParamInput, , 4)

Set rst = cmd.Execute 
Debug.Print rst.Fields(0)

End With

结束于