Multi Statement UDFs
Multi Statement User Defined Functions are very similar to
Stored Procedures. They both allow complex logic to take place inside the
function. There are a number of restrictions unique to functions though. The
Multi Statement UDF will always return a table variable--and only one table
variable. There is no way to return multiple result sets. In addition, a User
Defined Function cannot call a Stored Procedure from inside itself. They also
cannot execute dynamic SQL. Remember also, that UDFs cannot use non-deterministic
built in functions. So GETDATE and RAND cannot be used. Error handling is
restricted. RAISERROR and @@ERROR are invalid from inside User Defined
Functions. Like other programming languages, the purpose of a User Defined
Function is to create a stand-alone code module to be reused over and over by
the global application.
For a Multi Statement test, we will create a modified
version of the LookByFName function. This new function will accept the same
input parameter. But rather than return a table from a simple select, a
specific table will be created, and data in it will be manipulated prior to the
CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))
RETURNS @Result TABLE
INSERT INTO @Result
SELECT fname, hire_date
WHERE LEFT(fname, 1) = @FirstLetter
SET on_probation = 'N'
SET on_probation = 'Y'
WHERE hire_date < '01/01/1991'
To use the new function, execute:
SELECT * FROM dbo.multi_test('A')
With the new Multi Statement Function, we can manipulate
data like a Stored Procedure, but use it in statement areas like a View.
For example, only specific columns can be returned.
SELECT fname FROM dbo.multi_test('A')
The function can also be joined like a view:
SELECT e.lname, f.fname
FROM employee e INNER JOIN dbo.multi_test('A') f ON
e.fname = f.fname
User Defined Functions offer an excellent way to work with
code snippets. The main requirement is that the function be self-contained. Not
being able to use non-deterministic built in functions is a problem, but if it
can be worked around, UDFs will provide you with a programming plus.
See All Articles by Columnist Don Schlichting