Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 7, 2004

User Defined Functions in Microsoft SQL Server 2000 - Page 2

By Don Schlichting

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date