Home arrow SQL Server arrow Page 2 - Creating User Defined Functions In SQL Server 2000

Creating User Defined Functions In SQL Server 2000

Microsoft SQL Server 2000 allows us to create our own functions and call them through code. These functions are known as UDF's (User Defined Functions). In this article Mitchell's going to teach us how to create three different types of UDF's and how to work with them through batches of SQL code.

Author Info:
By: Mitchell Harper
Rating: 4 stars4 stars4 stars4 stars4 stars / 143
January 24, 2002
  1. · Creating User Defined Functions In SQL Server 2000
  2. · What is a user-defined function?
  3. · Creating a scalar UDF
  4. · Creating an inline-table valued UDF
  5. · Creating a multi-statement table valued UDF
  6. · Conclusion

print this article

Creating User Defined Functions In SQL Server 2000 - What is a user-defined function?
(Page 2 of 6 )

A UDF is a module, which is attached to a database in Microsoft SQL Server 2000. UDF's can accept parameters and can return a value. If you're familiar with stored procedures, then you'll be glad to know that UDF’s can contain code in a similar way to stored procedures. UDF's, however are limited to modifying only local variables (those whose lifetime is within the scope of the UDF), meaning that you can’t use SELECT, INSERT, DELETE, etc statements to modify any tables outside of the scope of the UDF.

What good are UDF's if you can't modify relational data then? Well, they're great for creating functions that need to work with data from databases, but don't actually need to modify the database. You can still retrieve and manipulate tables but can't physically commit changes to them.

UDF's support a wide range of functionality and just because you can’t commit write changes to a database, it doesn't mean that you should turn a blind eye to them. Here's a list of the statements that you can include in the body of a UDF:
  • Flow control statements such as IF…ELSE, CASE, GOTO labels, etc.
  • UPDATE, INSERT and DELETE commands to modify the contents of tables which are local to the UDF and have been created using SQL Server 200's news TABLE variable type.
  • The EXECUTE command to execute stored procedures and extended stored procedures.
  • The SET and SELECT statements to assign a value to a variable.
  • SELECT statements to retrieve field values from tables into local variables.
  • CURSOR, OPEN CURSOR, CLOSE CURSOR, DEALLOCATE CURSOR and FETCH NEXT FROM statements as long as the cursor is both created and disposed of within the scope of the UDF. Note that actual rows can't be returned, and they must be explicitly returned into type-matching variables using the FETCH NEXT INTO statement.
  • DECLARE statements to create variables local (in terms of scope) to the UDF.
  • Internally defined SQL variables which are prepended with "@@", such as @@ERROR and @@FETCH_STATUS.
As you may've guessed, a UDF is in some ways similar to a stored procedure. Here’s a list of how UDF's are different to stored procedures:
  • Scope of modification: UDF's aren't allowed to modify the physical state of a database using INSERT, UPDATE or DELETE statements. They can only work with local data.
  • Output parameters: UDF's don't have the ability to return output parameters to the calling function. They do however let us return a single scalar value or a locally created table.
  • Error handling: In UDF's, if an error occurs during a call to a stored procedure or a trigger, then that statement is terminated and flow control continue through the UDF.
  • Calling conventions: One of the major differences between UDF's and stored procedures is that a UDF can be called through a SQL statement without using the EXECUTE statement.
Now that we've got a better understanding of what a user-defined function actually is, let's create some example UDF's.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials