Application Security Testing: An Integral Part of DevOps
Views and stored procedures allow you to modularize and decouple your Transact-SQL (T-SQL) code well, but have you ever wanted to get more out of them? Like using a parameter-driven view or a stored procedure in a SELECT statement? Well, SQL Server has an often-overlooked alternative to views and stored procedures that you should consider: table-valued user defined functions (UDFs). Table-valued UDFs have all the important features of views and stored procedures and some additional functionality that views and stored procedures lack.
For example, my development team used table-valued UDFs to add a new layer of filtering to an ancient ASP business-intelligence application. Substituting parameter-driven UDFs for tables in the FROM clause of the select statements, we left the core T-SQL largely unchanged. Even better, we were able to automate most of the changes using "search and replace."
Using modified samples from the AdventureWorks sample database that ships with SQL Server 2005, this article explains how to use table-valued UDFs effectively in your T-SQL code. To see the UDFs stored in the AdventureWorks database, use Management Studio to navigate to the area in the database shown in Figure 1.
Figure 1. Location of UDFs in AdventureWorks Database
Every UDF is written in T-SQL (like a stored procedure or view) and has parameter values (like a stored procedure). You use the CREATE FUNCTION statement to define a UDF.
User-Defined Functions Defined
User-defined functions come in the following two flavors:
- Scalar-valued UDFs can return almost any single data type.
- Table-valued UDFs return a temporary table containing multiple rows.
You can use scalar UDFs inside any DML (INSERT, UPDATE, SELECT, and so forth) or T-SQL statement. Table UDFs have a few more limitations, and you typically use a table-valued UDF in the FROM part of a SELECT statement. This article focuses on the uses of table-valued UDFs.
Introduction to table-valued UDFs
There are two types of table-valued UDFs:
- Inline table-valued functions return a TABLE datatype. They each contain a single T-SQL statement.
- Multi-statement table-valued functions return a defined table. They can contain multiple T-SQL statements.
Both types of table-valued UDF return single result sets. The following is an example of an inline table-valued UDF definition:
CREATE FUNCTION dbo.TestInlineFunctionName ( ) RETURNS TABLE AS RETURN ( SELECT 0 as RetVal,* from [Person].[Contact] )
A multi-statement UDF looks similar to an inline UDF, but with one major difference: It contains table definition statements after the RETURNS directive, as in the following sample code:
RETURNS @retContactInformation TABLE ( -- Columns returned by the function [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL )
Because a multi-statement UDF can include many SELECT statements (as well as other T-SQL statements), it must explicitly populate the returning table, as in the following sample code:
INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
A multi-statement table-valued UDF enables you to do things such as modify the contents of the returned table. For example, the following code is perfectly legal in a multi-statement UDF:
UPDATE @retContactInformation SET [JobTitle] = 'None'
Now that you are familiar with table-valued functions, it's time to learn how they can replace views and stored procedures.
Replacement for Views
Table-valued UDFs work a lot like parameter-driven views. So, by using a table-driven UDF, you get all the flexibility of a view with the added benefit of parameters to filter data and act as an added layer of security. Because a table UDF requires permission to execute (like a view), you can use table UDFs as a sort of security mechanism.
The following code illustrates how to use a table-valued UDF in a SELECT statement:
select * from ufnGetContactInformation_MDY(1209)
Notice how the table UDF can replace a view in the FROM clause of the SELECT statement, and it also accepts a parameter. Although the parameter is hard-coded in the example code, you can pass variables to the function.
Table-valued UDFs also are a nice alternative to using multiple views for filtering a result set. For example, you can utilize IF/ELSE statements in a table UDF to filter on different parameter values. In the development scenario I mentioned previously, had we not used table-valued UDFs instead of views, we would've needed IF/ELSE statements in every stored procedure. In a database with 50+ stored procedures, that amounts to a lot of coding. Using the table UDFs, we could confine the required IF/ELSE statements to five functions.
Also, as with a view, the order by statement is invalid in table-valued functions./p>
Finally, as the article shows later, table-valued UDFs have most of the capabilities of a stored procedure.
Replacement for Stored Procedures
Table-valued UDFs are functionally similar to views, but structurally similar to stored procedures. Like a stored procedure, a table-valued UDF contains one or more T-SQL statements and up to 1,024 parameters. Also like a stored procedure, you can declare variables and use other functions, though there are limitations (more on these later).
Like a stored procedure, table UDFs allow common table expressions, as in the following recursive example:
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports )
As discussed previously, unlike a stored procedure, a table-valued UDF can be used in the FROM clause of a SELECT statement. In the development scenario mentioned previously, being able to add the table UDF to the FROM clause saved us from having to completely rewrite all 50+ stored procedures using dynamic code and the EXEC statement.
Table-valued UDFs are also more functional than stored procedures in some other interesting ways. As with a correlated subquery, single-value result sets can be used as parameters of table-value UDFs. For example, the following code below is legal:
select * from ufnGetContactInformation_MDY ((SELECT MAX(ContactID)FROM Person.Contact))
Functionality, of course, comes with a price. As such, table-valued UDFs have some usability limitations.
Table-Valued UDF Limitations
Like everything in software development, functionality comes with trade-offs. As you might imagine, there is a practical limit on the amount of data you should return in a table UDF. A table UDF's data limitations are similar to the limitations on a temporary table. Because you can add parameters to a table UDF, you usually can avoid these limitations by adding more parameters to filter the result set.
Unlike a stored procedure, a table UDF limits you to returning one result set.
Table UDFs are limited to using deterministic functions. Nondeterministic functions are not allowed. SQL Server books online defines nondeterministic functions as functions that have no predictable return value given their input. For example, you can't use the EXEC statement or even GETDATE() in a table UDF.
There are also limitations on how you handle text and image fields. Data returned from a text or image field is limited to 256 bytes, and you cannot use the READTEXT, WRITETEXT, and UPDATETEXT statements.
As you have seen, table UDFs are fine alternatives to using views and stored procedures. They offer similar functionality to views and similar structure to stored procedures, enabling you to take advantage of the rich coding capabilities that views lack and the flexibility to use them anywhere in a SELECT statement, which stored procedures lack.