User-defined functions (UDFs) – Part-2

Author: Basit A. Farooq

Editor’s Note: In this second of the two part article series, you’ll about creating and using in-line table functions, and multi-statement table functions. You used CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, and SQL Server Management Studio to create and manage functions. You learned about a related object, CLR aggregates.

Creating a user-defined table-valued function

We can create two types of table-valued functions: inline table-valued functions and multistatement table-valued functions. The inline table-valued function simply returns a result set from a query, and the multistatement table-valued function offers the ability to include logic within the body of the function and returns the result set on the basis of that logic. The syntax to create both types of table-valued functions is described as follows:

The syntax for an inline table-valued function is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,…n ]])
RETURNS TABLE
[ WITH [ ,…n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ][ ; ]

When you create an in-line table function:

• The RETURNS specification is TABLE.
• The table is based on the result of a single SELECT statement.
• Column names and data types are taken from the SELECT statement result.
• You can’t specify table constraints on the table returned.
• You can specify the ENCRYPTION and SCHEMABINDING options.
• The function doesn’t support the EXECUTE AS option.

The syntax for a multistatement table-valued function is as follows:

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [READONLY] }

[ ,…n ]])

RETURNS @return_variable TABLE

[ WITH [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN

END[ ; ]

The following example returns a two-column table based on a simple join statement. This function doesn’t include any input parameters.

CREATE FUNCTION Sales.fn_invsum

()

RETURNS TABLE

AS

RETURN (SELECT c.[name], h.[OrderID]

FROM Sales.BusinessCustomer c, Sales.[Order Head] h

WHERE c.customerid = h.[customerid])

To retrieve the table, you can run:

SELECT * FROM Sales.fn_invsum()

The syntax for creating a multi-statement function is:

CREATE FUNCTION [schema.]function_name

([@parameter_name _data_type [= default]])

RETURNS @return_variable TABLE (table_definition)

[WITH function_options] [AS]

BEGIN

SQL_statements

RETURN

END

Multi-statement table functions support the ENCRYPTION, SCHEMABINDING, and EXECUTE AS options. You must include the table variable definition when you create a multi-statement function. The table variable definition can include:

• Columns and data types
• Identity property
• ROWGUIDCOL property
• Collation (for char, varchar, nchar, and nvarchar only)
• Nullability
• Primary key constraint
• Unique constraint
• Check constraints
• Default definitions

Foreign key constraints aren’t supported. You can include index options, including creating the index as clustered or nonclustered and specifying index padding, when creating primary key and unique key constraint definitions.

CREATE FUNCTION Sales.fn_listin

(@test bit)

RETURNS @fn_list TABLE

([Order Number] int primary key not null,

[Name] varchar(40), [Date] datetime)

AS

BEGIN

–Check the value of @test

IF @test = 1

–If 1, return business customer orders

INSERT @fn_list SELECT o.OrderID,

b.[name] AS [Business], o.OrderDate AS [Date]

FROM Sales.[Order Head] o, Sales.BusinessCustomer b

WHERE o.CustomerID = b.CustomerID AND o.IsBusiness = 1

ORDER BY o.OrderID

ELSE

–If 0, return individual customer orders

INSERT @fn_list SELECT o.OrderID, c.FirstName + ‘ ‘ +

c.LastName AS [Contact],

o.OrderDate AS [Date]

FROM Sales.[Order Head] o, Sales.IndividualCustomer i,

Sales.Contact c

WHERE o.CustomerID = i.CustomerID AND

i.ContactID = c.ContactID AND o.IsBusiness = 0

ORDER BY o.OrderID

–Return the table

RETURN

END

You define a table variable with:

• Three columns named Order Number, Name, and Date.
• The Order Number column used as the primary key.

The function checks the value of @test and inserts a list of orders for either business or individual customers into @fn_list. You then return the @fn_list as a result.

To retrieve business customers, you run:

SELECT * FROM Sales.fn_listin(1)

To retrieve individual customers, you run:

SELECT * FROM Sales.fn_listin(0)

CLR functions

The basic process for creating a CLR function is similar to that for creating a Transact-SQL function. When you create a CLR function:

• You can create scalar or table-valued functions.
• CLR functions support ENCRYPTION and EXECUTE AS.
• CLR functions don’t support SCHEMABINDING.
• If you specify the RETURNS NULL ON NULL INPUT option, the function returns NULL if NULL is passed for any parameter.

You can explicitly set CALLED ON NULL INPUT or RETURNS NULL ON NULL INPUT on the registered assembly for a .NET Framework assembly method. If there is a conflict between the method’s setting and that specified in CREATE FUNCTION, the CREATE FUNCTION setting takes precedence.

The primary difference is that, instead of SQL statements, you specify a registered .NET Framework assembly module by using the syntax:

EXTERNAL NAME assembly_name.class_name.module_name

You must register the assembly before you can use it to create a stored procedure. Detailed procedures for working with .NET assemblies and CLR functions are beyond the scope of this article.

CLR aggregates

You use the CREATE AGGREGATE command to create a CLR aggregate function. You use these as you would the system aggregate functions provided with SQL Server.

The general syntax for using CREATE AGGREGATE is:

CREATE AGGREGATE [schema.]function_name

(@parameter_name data_type)

RETURNS data_type

EXTERNAL NAME assembly_name.class_name.module_name

• The data types specified must be scalar data types.
• The text, ntext, and image data types aren’t supported.

Once again, you must register the assembly before you can use it to create a stored procedure. Any additional discussion about CLR aggregate functions is beyond the scope of this article.

Managing functions

You can use ALTER FUNCTION, DROP FUNCTION, and the SQL Server Management Studio to managed UDFs.

Managing UDFs by using SQL Server Management Studio

To modify a function in SQL Server Management Studio:

• Expand the database, Programmability, and Functions.
• Expand the function type (Table-valued Function, Scalar-valued Function, or Aggregate Function).
• Right-click the function and choose Modify. This opens a new query window with the ALTER FUNCTION statement loaded. Modify and execute the ALTER FUNCTION statement. This isn’t supported for functions created by using the WITH ENCRYPTION option. In addition to modifying the function, you can right-click a function and:
  • Script the function to a file or query window.
  • Rename the function.

Modifying UDFs by using the ALTER FUNCTION command

We use the ALTER FUNCTION statement to modify a function. It uses the same syntax as the CREATE FUNCTION statement.

For scalar-value functions, the syntax is as follows:

ALTER FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]])

RETURNS return_data_type

[ WITH [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END [ ; ]

For inline table-valued function, the syntax is as follows:

ALTER FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]])

RETURNS TABLE

[ WITH [ ,…n ] ]

[ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

For multistatement table-valued functions, the syntax is as follows:

ALTER FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ = default ] [READONLY] }

[ ,…n ]])

RETURNS @return_variable TABLE

[ WITH [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN

END [ ; ]

Typically, you shouldn’t modify a function by dropping and recreating the function. This resets the function to default permissions. It also affects any dependent objects, including:

• Stored procedures
• Triggers
• Other functions

You can avoid resetting permissions by using ALTER FUNCTION to modify the function. Dependent objects aren’t affected by the operation.

Dropping functions

We use the DROP FUNCTION statement to permanently delete a user-defined function. The DROP FUNCTION statement syntax is as follows:

DROP PROC[EDURE] [schema.]function_name

You can specify one or more functions in the DROP FUNCTION command.

The following is an example of the DROP FUNCTION statement:

DROP FUNCTION [Sales].[fn_listin];

The following are the steps to drop a function in SQL Server Management Studio 2014:

• In Object Explorer, expand the Databases folder.
• Expand the database where the function you want to delete exists.
• Expand Programmability.
• Expand Functions.
• Expand the appropriate user-defined function folder.
• Right-click on the function you want to delete, and then choose Delete from the context menu.
• SQL Server prompts you to verify your action. Click on OK to confirm.

Viewing functions

To view a list of functions in SQL Server Management Studio:

• Expand the database, Programmability, and Functions.
• Expand or select the function type (Table-valued Function, Scalar-valued Function, or Aggregate Function).

You can also:

• Expand the function and Parameters to view function input parameters.
• Right-click a function and choose View Dependencies to view object dependencies.
• Right-click a function and choose Properties to view function properties.

SQL Server support the following function properties:

• General — Includes the creation statement and general properties (read-only).
• Permissions — View and set function permissions.
• Extended Properties — View extended properties (if any).

Viewing function definition

The following items let you view function definition information:

• sys.sql_modules
• OBJECT_DEFINITION
• sp_helptext

Note: If a function was created using WITH ENCRYPTION, you cannot use the sp_helptext system stored procedure and nor can you query the sys.sql_modules and sys.syscomments system views to view its definition.

Viewing information about a function

You view information about functions with:

• sys.objects
• sys.parameters
• sp_help

Viewing function dependencies

To view dependencies, you use:

• sys.sql_dependencies
• sp_depends

Conclusion

In this two part article series, you compared and contrasted procedures for creating and using scalar value functions, in-line table functions, and multi-statement table functions. You used CREATE FUNCTION, ALTER FUNCTION, DROP FUNCTION, and SQL Server Management Studio to create and manage functions. You learned about a related object, CLR aggregates.