Chapter 10: User-defined Functions

The ability to design user-defined Transact-SQL functions (UDF) was introduced in SQL Server 2000. In earlier versions, you were only able to use built-in functions. Apart from the ability to create them in .NET programming languages (that we will cover later), there were not many changes to UDFs in SQL Server 2005. This chapter will focus on the design of UDFs in Transact-SQL.

Design of Scalar User-defined Functions

User-defined functions can be created using the Create Function statement, changed using Alter Function, and deleted using Drop Function. You can use sp_help and sp_stored_procedures to get information about a function, and sp_helptext to obtain its source code. From Management Studio, you can use the same technique to manage user-defined functions that you used to create and manage stored procedures.

Functions can accept zero, one, or more input parameters, and must return a single return value. The returned value can be scalar or it can be a table. Input parameters can be values of any data type except timestamp, cursor, and table. Return values can be of any data type except timestamp, cursor, text, ntext, and image.

The Create Function statement has the following syntax:

 Create Function [schema_name.]function_name (      [ {@parameter_name scalar_data_type [= default]} [,...n] ] } returns scalar_data_type         |Table         |return_variable Table ({ column_def table_constraint} [, ...n] ) [With {Encryption | Schemabinding} [,...n] ] [As] {Begin function_body End} | Return [(] {value | select-stmt} [)] 

The following example produces a function that will return the quarter for a specified date:

 Create Function util.fnQuarterString -- returns quarter in form of '3Q2000'.      (      @dtmDate datetime      ) Returns char(6) -- quarter like 3Q2000 As Begin      Return (DateName(q, @dtmDate) + 'Q' + DateName(yyyy, OdtmDate)) End 

To reference a function, you must specify both the schema and the object identifier. It is not possible to fall back on default schema:

 Select util.fnQuarterString(GetDate() ) 

The function in the previous example had just one Return statement in the body of the function. In fact, a function can be designed with flow control and other Transact-SQL statements. A function can even contain more than one Return statement. Under different conditions, they can serve as exit points from the function. The only requirement is that the last statement in the function body be an unconditional Return statement. The following function illustrates this principle in returning a date three business days after the specified date:

 Create Function util.fnThreeBusDays -- returns date 3 business days after the specified date      (OdtmDate datetime) Returns datetime As Begin Declare @inyDayOfWeek tinyint Set @inyDayOfWeek = DatePart(dw, OdtmDate) Set @dtmDate = Convert(datetime, Convert(varchar, @dtmDate, 101}} If @inyDayOfWeek = 1 -- Sunday      Return DateAdd(d, 3, @dtmDate ) If @inyDayOfWeek = 7 -- Saturday      Return DateAdd(d, 4, @dtmDate ) If @inyDayOfWeek = 6 -- Friday      Return DateAdd(d, 5, @dtmDate ) If @inyDayOfWeek = 5 -- Thursday      Return DateAdd(d, 5, @dtmDate ) If @inyDayOfWeek = 4 -- Wednesday      Return DateAdd(d, 5, @dtmDate ) Return DateAdd(d, 3, @dtmDate ) End 

Side Effects

User-defined functions have one serious limitation—they cannot have side effects. A. function side effect is any permanent change to resources (such as tables) that have a scope outside of the function (such as a nontemporary table that is not declared in the function). Basically, this requirement means that a function should return a value while changing nothing in the database.

Tip 

In some development environments like C or Visual Basic, a developer can write a function that can perform some additional activities or changes, hut it is a matter of good design and discipline not to ahuse that opportunity.

SQL Server prevents you from creating side effects by limiting which Transact-SQL statements can be used inside a function:

• Assignment statements (Set or Select) referencing objects local to the function (such as local variables and a return value)

• Flow control statements

• Update, Insert, and Delete statements that update local table variables

• Declare statements that define local variables or cursors

• Statements that declare, open, close, fetch, and deallocate local cursors (the only Fetch statements allowed are ones that retrieve information from a cursor into local variables)

Use of Built-in Functions

User-defined functions cannot call built-in functions that return different data on each call, such as these:

@@CONNECTIONS	@@TOTAL_ERRORS
@@CPU_BUSY	@@TOTAL_READ
@@IDLE	@@TOTAL_WRITE
@@IO_BUSY	GetDate()
@@MAX_CONNECTIONS	GetUTCDate()
@@PACK_RECEIVED	Newld()
@@PACK_SENT	Rand()
@@PACKET_ERRORS	TextPtr()
@@TIMETICKS

Notice that GetDate() is among the forbidden functions. If you try to use it inside a user-defined function, SQL Server will report an error, as shown in Figure 10-1.

Figure 10-1: Limitation on use of built-in functions in user-defined functions

Encryption

As is the case with stored procedures, functions can be encrypted so that nobody can see their source code. You just need to create or alter the function using the With Encryption option.

Schema-binding

A new option, With Schemabinding, allows developers to schema-bind a user-defined function to database objects (such as tables, views, and other user-defined functions) that it references. Once the function is schema-bound, it is not possible to make schema changes on underlying objects. All attempts to drop the objects and all attempts to alter underlying objects (which would change the object schema) will fail.

A function can be schema-bound only if all of the following criteria are satisfied:

• All existing user-defined functions and views referencing the objects referenced by the function must already be schema-bound.

• All database objects that the function references must reside in the same database as the function. References to database objects cannot have server or database qualifiers. Only object owner qualifiers and object identifiers are allowed.

• The user who executes the Create (or Alter) Function statement must have References permissions on all referenced database objects.