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

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
TABLE OF CONTENTS:
  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
SEARCH DEVARTICLES

Creating User Defined Functions In SQL Server 2000 - Creating a scalar UDF
(Page 3 of 6 )

In SQL Server 2000, there are three types of user-defined functions that we can create. Each one is created using SQL Server 2000's "CREATE FUNCTION" statement, but they differ in their actual calling convention. In this section we're going to discuss scalar functions, which can accept up to 1,024 input parameters and return one scalar (simple data type) value.

Here's the simplified signature of the "CREATE FUNCTION" statement for scalar functions:

CREATE FUNCTION [ owner_name. ] function_name

(

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

)

RETURNS data_type

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END


Let's run through the details of using the "CREATE FUNCTION" statement to create our own UDF. On the first line, we must specify the name that we want our UDF to be known as. We can also optionally specify the owner of the UDF, like this:

CREATE FUNCTION dbo.myFunction

Next, we have the optional parameter list. These are input parameters, and can be passed into our UDF from within SQL code. Here's a parameter list that accepts two integer values and one variable character value (just like stored procedures, we can reference these input parameters in the body of our UDF):

@int1 INT,

@int2 INT,

@vc1 VARCHAR(50)


The RETURNS statement tells SQL Server the type of scalar variable that our function will be returning. Note that UDF's can't return timestamp, text, ntext or image variable types. If, for example, we wanted to specify that our UDF would return an integer, then we would use the RETURNS statement like this:

RETURNS INT

Next is the optional AS keyword, which is used to explicitly tell SQL Server where the function body starts. The actual body of our UDF is surrounded with BEGIN and END keywords, which denote a block of code in SQL Server 2000:

BEGIN

-- Body of our UDF goes here

-- RETURN statement and value go here

END


Lastly, we have the RETURN statement, which allows us to return a value back to whoever called our UDF. The value after the RETURN keyword should be of the same type as the keyword specified after the "RETURNS" statement earlier in the "CREATE FUNCTION" statement:

CREATE FUNCTION dbo.myFunction

()

RETURNS INT

AS

BEGIN

DECLARE @myInt INT

SET @myInt = 1

-- Function body goes here

RETURN @myInt

END


As you can see in the example above, we have told SQL Server that our UDF will return an integer type using the "RETURNS INT" statement. In the actual body of our UDF, the "RETURN @myInt" statement returns the value of the declared integer variable, @myInt, which is 1.

Let's create a simple user-defined function that will multiply two numbers together and return that number as its result. Open Query Analyzer (Start -> Programs -> Microsoft SQL Server -> Query Analyzer) and connect to your database with administrative rights.

Enter and execute the following code in the query analyzer window (I will assume that you will be creating your UDF's under the "pubs" database, which is a default database that comes pre-installed with SQL Server 2000):

USE PUBS

GO

CREATE FUNCTION dbo.mult

(

@num1 INT,

@num2 INT

)

RETURNS INT

AS

BEGIN

RETURN (@num1 * @num2)

END


Our new function, named "mult", accepts two integer values and returns the product of those two values. To test our UDF, delete all of the code in the query window and enter the following code:

USE PUBS

GO

DECLARE @result INT

SET @result = dbo.mult(5, 10)

PRINT @result


Output into the results window should look like this:

The result of executing our scalar UDF

You'll notice that I've called our scalar UDF using a two-part name in the form of ownerName.functionName. I could've also used a three-part name in the form of databaseName.ownerName.functionName such as "PUBS.dbo.mult". Using a two/three part name to call our UDF is mandatory and helps SQL Server 2000 to distinguish between our UDF's and system level functions.
blog comments powered by Disqus
SQL SERVER ARTICLES

- 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 
Support 

Developer Shed Affiliates

 




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