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 return:
CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))
RETURNS @Result TABLE
(
fname varchar(20),
hire_date datetime,
on_probation char(1)
)
AS
BEGIN
INSERT INTO @Result
(fname, hire_date)
SELECT fname, hire_date
FROM employee
WHERE LEFT(fname, 1) = @FirstLetter
UPDATE @Result
SET on_probation = 'N'
UPDATE @Result
SET on_probation = 'Y'
WHERE hire_date < '01/01/1991'
RETURN
END
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

Conclusion
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