Creating User Defined Functions In SQL Server 2000

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:



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