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
applications.

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.

Weekly .NET tips in your inbox

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET.

Automatically sign up today!

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.

Scalar values

The following code sample demonstrates a scalar UDF that
returns the date a database was created.

CREATE FUNCTION dbo.UDF_DatabaseCreationDate 

(@databaseNamesysname)

RETURNS datetime

AS

BEGIN

DECLARE @creationDatedatetime

SELECT @creationDate = crdate FROM master.dbo.sysdatabases

WHERE name = @databaseName

RETURN (@creationDate)

END

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:

2000-08-06 01:41:00.310

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
database:

CREATE FUNCTION dbo.UDF_GetEmployeeName

(@employeeID As int)

RETURNS nvarchar(40)

BEGIN 

DECLARE @fullName As nvarchar(40)

SELECT @fullName = FirstName + ' ' + LastName

FROM Employees

WHERE EmployeeID = @employeeID

RETURN (@fullName)

END

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
following URL:

http://localhost/UDF1VB.aspx?empID=1

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.

Table values

UDFs may return results as tables
in the following manner:

  • Inline
    table-valued functions return the result of a single SELECT statement.
  • Multi-statement
    table-valued functions return a table constructed via numerous T-SQL
    statements.

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
result set:

CREATE PROCEDURE dbo.ReturnAllEmployees
AS
BEGIN
SELECT dbo.GetEmployeeName(e.EmployeeID) As FullName,
Address, City, PostalCode
FROM Employees e
END

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
equivalent C#.

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
column.

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.