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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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

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