ADO: Deriving parameter information for user-defined, table-valued functions

(The content of this entry was developed by Anton Klimov, a software engineer on the SQL Server Native Client team.)

Table-valued, user-defined functions (TVF) can be used as an alternative to views or stored procedures. (

https://technet.microsoft.com/en-us/library/ms191165.aspx)

There is a problem, however, with the existing support for obtaining parameter information from classic (VB script) ADO code -- if you call a TVF as if it were a stored procedure, it is possible to derive parameter information, but execution will fail.

If one tries to prepare TVF as a select statement it is not possible to obtain the parameter information from a SQL OLE DB provider because providers do not support obtaining parameter information for parameters in the ‘FROM’ clause.

The example below obtains the parameter information and then uses it for the parameterized select statement.

Sub ADO_example()

Dim cn As New ADODB.connection
Dim param1 As ADODB.Parameter

cn.ConnectionString = "provider=sqloledb;integrated security=sspi;Data Source=akl3vm1; initial catalog=tempdb"
cn.Open

cn.Execute "if OBJECT_ID('f_distance', 'IF') is not null drop function f_distance"
cn.Execute "create function f_distance (@x int, @y int) returns table " _
& "as return select @x as x, @y as y, sqrt(@x*@x+@y*@y) as distance"

Set cmd1 = CreateObject("ADODB.Command")

cmd1.CommandText = "f_distance"
cmd1.CommandType = adCmdStoredProc
cmd1.ActiveConnection = cn

cmd1.Parameters.Refresh

Rem At this point you will get the parameters collection starting with the retun value
Rem which we will not use.
Rem The command text will be like: "{?=call f_distance(?,?)}"

Rem The 3 lines below would produce "The parameter is incorrect"

'cmd1.Parameters("@x") = 1
'cmd1.Parameters("@y") = 2
'Set rs = cmd1.Execute

Rem We will use a second command
Set cmd2 = CreateObject("ADODB.Command")
cmd2.CommandText = "select * from f_distance(?,?)"
cmd2.CommandType = adCmdText
cmd2.ActiveConnection = cn

Rem The line below would produce "Syntax error or access violation"
'cmd2.Parameters.Refresh

For i = 1 To cmd1.Parameters.Count - 1
Set param1 = cmd1.Parameters(i)
cmd2.Parameters.Append param1
Debug.Print param1.Name, param1.Type
Next i

cmd2.Parameters("@x") = 1
cmd2.Parameters("@y") = 2

Set rs = cmd2.Execute

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Debug.Print rs(i).Name, rs(i).Value
Next i
rs.MoveNext
Loop

End Sub