Microsoft® SQL Server® 2012 Bible

455

Chapter 18: Building User-Defi ned Functions

18

If you’d like to see the metadata associated with UDFs in your database, the system view sys.sqlmodules pro-

vides an alternative to Management Studio.

USE AdventureWorks2012;

GO

SELECT definition, type

FROM sys.sql_modules AS m

JOIN sys.objects AS o ON m.object_id = o.object_id

AND type IN ('FN', 'IF', 'TF');

GO

Scalar Functions

A scalar function is one that returns a single, specifi c value. The function can accept multi-

ple parameters, perform a calculation, and then return a single value. For example, a scalar

function could accept three parameters, perform a calculation, and return the answer.

Within the code of a scalar function, the value is passed back through the function by

means of a RETURN command. Every possible codepath in the user-defi ned function should

conclude with a RETURN command.

Scalar user-defi ned functions may be used within any expressions in SQL Server, even

expressions within check constraints (although this isn’t recommended practice).

Understanding Limitations

The scalar function must be deterministic, meaning it must repeatedly return the same

value for the same input parameters. For this reason, nondeterministic functions — such as

newid() and rand() — are not allowed within scalar functions. User-defi ned scalar

functions are not permitted to update the database, call stored procedures, or call DBCC

commands, with the single exception that they may update table variables. They cannot

return binary large object (BLOB) data such as text, ntext, timestamp, and image data-

type variables, nor can scalar functions return table variables or cursor data types. For

error handling, UDFs may not include TRY...CATCH or RAISERROR.

A user-defi ned function may call other user-defi ned functions nesting up to 32 levels deep,

or it can call itself recursively up to 32 levels deep before it blows up. However, this limita-

tion is academic. Nesting functions can have serious performance implications; avoid this if

at all possible.

c18.indd 455c18.indd 455 7/30/2012 5:41:44 PM7/30/2012 5:41:44 PM

http://www.it-ebooks.info