Executing User-Defined Functions

Executing a user-defined function is similar to executing a prepared Transact-SQL statement, except that the user-defined function exists as a permanent object in the database. Executing a user-defined function can increase the efficiency of an application because it can reference complex Transact-SQL statements at the server instead of from an application.

This example shows the execution of the fn_helpcollations built-in, user-defined function. All user-defined functions can be executed using the technique demonstrated in this example.

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Open "Provider=sqloledb;Data Source=MyServerName;" & _
   "Initial Catalog=northwind;User Id=sa;Password=;"

'Prepare the user-defined function statement and execute the command.
Cmd.ActiveConnection = cn
Cmd.CommandText = "select * from ::fn_helpcollations()"
Set rs = Cmd.Execute

rs.Close