Home arrow SQL Server arrow Page 5 - 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 - Creating a multi-statement table valued UDF
(Page 5 of 6 )

The third and final form of a UDF that we can create is the multi-statement table valued UDF. Multi-statement UDF's are extremely powerful, and just like inline table valued UDF's, they allow us to use a select statement to return a result set. What differentiates them from other UDF's however, is that they also allow us to specify the design of the table being returned from the UDF.

Here's the simplified signature of the "CREATE FUNCTION" statement for multi-statement table valued functions:

CREATE FUNCTION [ owner_name. ] function_name

( [ { @parameter_name [AS] data_type } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ AS ]





I know that it looks a bit confusing, but bear with me as we work through the details of each statement and an example. You'll be glad you did.

The first two lines of the function are no different to the other two types of UDF's that we've discussed, so we'll skip them. On the "RETURNS ..." line, we specify a variable of type TABLE that will be returned from the function, as well as the definition of each of the fields that will make up the table, like this:



userId INT,

name VARCHAR(50),

state CHAR(2)


In the actual body of our UDF, we use an insert statement to add rows to our table. We can use SQL's select statement to retrieve field values from any table in our database, just as long as that field's type matches the type specified in the table's field definitions:


INSERT @myTable

SELECT au_id,

au_fname + ‘ ‘ + au_lname as au_name,


FROM dbo.authors

WHERE @contract = 1


Let's use the "jobs" table of the "pubs" database to create a simple multi-statement table valued UDF. We will return a list of all jobs whose minimum level is greater than a certain number (which will be the input parameter to the function). Here's the design of the jobs table:

The design of the jobs table in the pubs database

Enter and execute the following code in query analyzer:









jobName VARCHAR(50),





INSERT @jobTable

SELECT job_desc,

(max_lvl - min_lvl)

FROM jobs

WHERE min_lvl >= @minLvl



See how I've explicitly defined the fields that our table variable will return: jobName, which is a variable character field, and difMinMax, which is a tiny integer field.

Between the BEGIN and END statements, we use a select statement to get every job from the jobs table whose min_lvl field is greater than or equal to the input parameter, @minLvl. Note that within this select statement, we could also grab field values from other tables to include in the resultant table, @jobTable.

To test our UDF, clear the code in the query window and enter and execute this code:



SELECT * FROM getJobsByMinLvl(125)

WHERE jobName LIKE '%Chief%'

We are referring to our UDF as a table, and passing in the parameter 125, which will return all jobs whose min_lvl field is greater than or equal to 125. Notice how I’ve also used a where clause to further filter the resultant table, only requesting rows where the jobName field contains "Chief". The table returned from the query above looks like this:

The table produced from our multi-table UDF
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-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials