465
Chapter 18: Building User-Defi ned Functions
18
Using Consistent Naming Conventions
There are few things more frustrating than trying to troubleshoot a query and not know-
ing that what you’re looking at is a user-defi ned function. Make sure to create a naming
convention of some sort for all user-defi ned functions. Name prefi xes seem to be the most
commonly adopted method to achieve this. If you want to go a step further, make your pre-
fi x indicate whether the UDF is a scalar, inline table-valued, or mutistatement table-valued
function. It will make your T-SQL easier to read and to troubleshoot. For example, an inline
table-valued function that returns the average monthly sales per product category might be
named ifn_AvgMonthlySalesPerCategory or udfAvgMonthlySalesPerCategory.Summary
User-defi ned functions expand the capabilities of SQL Server objects and open a world of
fl exibility within expressions and the SELECT statement.The big ideas from this chapter follow:■ Scalar user-defi ned functions return a single value and must be deterministic.■ (^) Inline table-valued user-defi ned functions are similar to views and return the
results of a single SELECT statement.
■ (^) Multistatement, table-valued, user-defi ned functions use code to populate a table
variable, which is then returned.
■ (^) The APPLY function can be used to pass data to an inline table-valued UDF or a
multistatement, table-valued UDF from the outer query, similar to how a correlated
subquery can receive data from the outer query.
T-SQL code can be packaged in stored procedures, user-defi ned functions, and triggers. The
next chapter delves into stored procedures.
c18.indd 465c18.indd 465 7/30/2012 5:41:48 PM7/30/2012 5:41:48 PM
http://www.it-ebooks.info