User-Defined Functions in AdventureWorks
The AdventureWorks sample OLTP database includes several Transact-SQL user-defined functions. Examples of common language runtime (CLR) user-defined functions are available in CLR Programmability Samples.
CLR User-Defined Functions
The following table lists the CLR user-defined function samples that are available. For more information about CLR user-defined functions, see CLR User-Defined Functions.
Sample |
Description |
|---|---|
Supplementary-Aware String Manipulation |
Shows the implementation of five Transact-SQL string functions that provide the same string modify functions as built-in ones, but with additional supplementary character-aware capability to handle both Unicode and supplementary character strings. The five functions are len_s(), left_s(), right_s(), sub_s(), and replace_s(). These are equivalent to the built-in string functions LEN(), LEFT(), RIGHT(), SUBSTRING(), and REPLACE(). |
In-Process Data Access Sample |
Contains several simple functions that demonstrate various features of the SQLCLR in-process data access provider. |
AdventureWorks Cycles CLR Layer |
Contains a currency conversion function that returns an instance of a currency user-defined type. |
UDT Utilities |
Includes functions to expose assembly metadata to Transact-SQL; sample streaming table-valued functions to return the types in an assembly as a table; and also functions to return the fields, methods, and properties in a user-defined type. Demonstrates technologies such as streaming table-valued functions, Microsoft .NET Framework reflection APIs, and invocation of table-valued functions from Transact-SQL. |
Transact-SQL User-defined Functions
The following table lists the Transact-SQL user-defined functions that are included in the AdventureWorks sample OLTP database. For more information about Transact-SQL user-defined functions, see Understanding User-defined Functions.
User-defined function |
Description |
Input parameters |
Return values |
|---|---|---|---|
ufnLeadingZeros |
Scalar function that adds leading zeros to a specified integer value. The function returns a varchar(8) data type. |
@valueint |
@ReturnValuevarchar(8) |
ufnGetContactInformation |
Simplifies user access to data by encapsulating a complex query in a table-valued function. |
@contactidint |
ContactIDint, FirstNamenvarchar(50), LastNamenvarchar(50), JobTitle nvarchar(50), ContactType nvarchar(50) |
ufnGetProductDealerPrice |
Scalar function calculating the dealer price for a specific product based on the list price of the product on a specified date. |
@productIDint, @orderdate datetime |
@DealerPricemoney |
Examples
A. Using dbo.ufnLeadingZeros
The following example uses the ufnLeadingZeros function to generate a customer account number from an existing CustomerID column in the Customer table.
USE AdventureWorks;
GO
SELECT CustomerID,('AW' + dbo.ufnLeadingZeros(CustomerID))
AS GenerateAccountNumber
FROM Sales.Customer
ORDER BY CustomerID;
GO
B. Using dbo.ufnGetContactInformation
The AdventureWorks database contains the names of employees, customers (store representatives or individual consumers), and vendor representatives in one table, the Contact table. The table-valued function ufnGetContactInformation, returns one row for a specified ContactID.
USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO
C. Using dbo.ufnGetProductDealerPrice
The following example uses the ufnGetProductDealerPrice function to return the discounted dealer price for all products in the ProductListPriceHistory table.
USE AdventureWorks;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
See Also