User Defined Functions in Microsoft SQL Server 2000 - Page 2

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