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

Question

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"

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

Edit 1: Examples Added Using Command Object

First the 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)

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?

Edit 2: test code modified to use 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

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

.CommandText = "dbo.ftblTest" 

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

.CommandText = "dbo.ftblTest()" 

error: An insufficient number of arguments were supplied for the procedure or function 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.

Answer 1

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.

Answer 2

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

And:

.CommandText = "dbo.ftblTest()"

Or:

.CommandText = "dbo.ftblTest"