First published on MSDN on May 20, 2008
(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. (
http://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
