USE LIBRARY Statement
This statement loads a function library for use during the session.
BNF
<Use-Library-statement> ::= USE LIBRARY <Library-Name-Clause>
Remarks
Use this statement to load a user-defined function.
User-defined function libraries should be implemented as COM components. These libraries can be implemented as in-process servers (in a .dll) or as local servers (in an .exe). Before loading a user-defined function library, ensure that the library contains a type library. Additionally, all of the interfaces defined in the type library must be derived from IDISPATCH for automation. User-defined function libraries can be developed in any environment capable of generating COM components.
Examples
The following examples demonstrate defining and using a user-defined function library.
A. Creating a User-Defined Function
In the following example, a Microsoft® Visual Basic® function is defined that converts currency based upon the exchange rate of a given country:
Public Function Convert(country As String, Value As Double) As Double
Select Case country
Case "USA"
Convert = Value * 1
Case "Canada"
Convert = Value * 1.5486
Case "Mexico"
Convert = Value * 9.93
End Select
End Function
B. Using a User-Defined Function Library
To use this function with Microsoft SQL Server™ 2000 Analysis Services, place it into a Visual Basic ActiveX® DLL Project. To load the library for use in Analysis Services, use the USE LIBRARY statement. In the following example, a user-defined function library is loaded for use during the session, and a query is defined that uses a query scoped calculated member containing the user-defined function:
USE LIBRARY "UDF.Currency"
WITH Member Measures.SalesNC AS
'UDF!_Currency!Convert(
[Sales].[Customers].[Country],
[Sales].[Measures].Members
)
SELECT {SalesNC} ON COLUMNS FROM Sales'