Share via

User-defined Functions in AdventureWorks

  • Article

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.

String Utilities Sample

Contains a streaming table-valued function, written in C# and Microsoft Visual Basic, that splits a comma-separated string into a table with one column. It also contains an aggregate function that converts a string column to a comma-separated string.

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

Other Resources

SQL Server Objects in AdventureWorks
CREATE FUNCTION (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance