Database interaction is a standard feature of most .NET
applications, with SQL Server as the popular choice for the data tier.
Consequently, .NET developers often find themselves developing T-SQL code
within their applications or stored procedures on the database server.
Developers often overlook a number of the features in the
T-SQL language. For instance, when I used T-SQL’s User Defined Function (UDF)
feature in a recent application, I was surprised by the lack of understanding
of this feature by other developers on the team. With that in mind, I want to take this opportunity to provide an overview of
the UDF feature and explain how you can incorporate it in your .NET
What is a UDF?
UDFs, which were introduced with
SQL Server 2000, allow the creation of T-SQL functions. These functions may be
embedded in other SQL statements, and they can return one of two data types: scalar
values or a table. Scalar values are single values that correspond to T-SQL
data types like varchar and int. Tables include a set
or rows that include one or more column values. Just like stored procedures,
they may accept parameters.
If you’re using SQL Server Enterprise Manager, each database
contains a UDFs item within the user interface. You
may right-click on this item to create a new UDF by selecting New User Defined
Function. Likewise, T-SQL code may be entered via the Query Analyzer client to
create a new function. The latter method is used in this article.
UDFs may be invoked from a query like
built-in functions. Also, they may be executed like stored procedures via the
execute statement. Let’s take a closer look at creating your own UDF. We begin
with scalar values.
The following code sample demonstrates a scalar UDF that
returns the date a database was created.
CREATE FUNCTION dbo.UDF_DatabaseCreationDate
SELECT @creationDate = crdate FROM master.dbo.sysdatabases
WHERE name = @databaseName
The following T-SQL statement may be used to execute this
function. Notice that the name of the popular Northwind
database is passed to the UDF:
SELECT dbo.UDF_DatabaseCreationDate ('Northwind')
It returns the following datetime value
on my machine:
Furthermore, a function could be used to return an employee’s
full name—the concatenation of first and last names—given the employee id. The
following T-SQL creates this function in the Northwind
CREATE FUNCTION dbo.UDF_GetEmployeeName
(@employeeID As int)
DECLARE @fullName As nvarchar(40)
SELECT @fullName = FirstName + ' ' + LastName
WHERE EmployeeID = @employeeID
This function could be utilized within an ASP.NET page to
return an employee’s name. The ASP.NET code in
Listing A accepts an employee id number via a querystring
variable. The result is that the employee’s name is displayed on the page via
the UDF call.
I load this ASP.NET page on my local machine using the
The name is displayed in the text box.
Listing B contains the equivalent C# code. Let’s move forward by working with UDFs
that return tables.
UDFs may return results as tables
in the following manner:
table-valued functions return the result of a single SELECT statement.
table-valued functions return a table constructed via numerous T-SQL
We could use our full name UDF inside a stored procedure
that returns information on all employees in the Northwind
employees table. The following stored procedure uses the UDF to construct its
CREATE PROCEDURE dbo.ReturnAllEmployees
SELECT dbo.GetEmployeeName(e.EmployeeID) As FullName,
Address, City, PostalCode
FROM Employees e
The UDF is called inline within another T-SQL statement. The
neat aspect of this approach is the employee id from the current row is passed to
the function, so the full name is easily constructed for each row returned by
the stored procedure. The ASP.NET in
Listing C displays the results of this stored procedure in a DataGrid
control. Listing D contains the
The code connects to SQL Server and calls the stored procedure.
The stored procedure’s result set is stored in a SQLDataReader
object. It is used as the data source for the ASP.NET DataGrid
control. The control’s AutoGenerateColumns property
is set so the columns are automatically created. The results are displayed in a
simple table with the UDF’s result in the first
UDFs add value to your .NET
While UDFs are a SQL Server
feature rather than a .NET feature, every
developer who works with SQL Server needs to know about UDFs. This valuable feature can be used to simplify
stored procedures or provide simple functionality that may be easily utilized
in your .NET code.