Creating User Defined Functions In SQL Server 2000

Creating User Defined Functions In SQL Server 2000 - Creating an inline-table valued UDF
(Page 4 of 6 )

The second form of UDF that we're going to create is the inline-table valued UDF. An inline-table valued UDF allows us to use a single select query in the body of our UDF to return a set of rows from any specific table.

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

CREATE FUNCTION [ owner_name. ] function_name

(

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

)

RETURNS TABLE

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

As you can see, the signature of the "CREATE FUNCTION" for an inline-table valued UDF is exactly the same as that for a scalar UDF accept that it returns a "TABLE" variable type and has no body.

[Note] The variable type "TABLE" is new to SQL Server 2000 and is used to store a set of table rows. [End Note]

Instead of containing a function body, inline-table valued UDF's specify a select statement after the RETURN keyword. This select statement is executed and all of its matching rows are returned as the value of the function.

Let's create a simple inline-table valued UDF that will return a list of authors from the "authors" table in the "pubs" database who are from California. The design of the author's table looks like this:



Using query analyzer, enter and execute the following code:

USE PUBS

GO

CREATE FUNCTION getAuthorsByState

(

@state CHAR(2)

)

RETURNS TABLE

AS

RETURN

(

SELECT au_fname + ' ' + au_lname AS aName

FROM authors

WHERE state = @state

)

We've just created a new UDF named "getAuthorsByState", which will return a list of names of authors whose state field matches the value passed in through the input parameter, @state.

As you can see, we have used a basic SQL query to concatenate the values of the au_fname and au_lname fields with a space. In the where clause, we tell SQL Server to only return authors whose state matches the value of our @state input parameter.

To test our new inline-table valued UDF, clear the code window in query analyzer and enter and execute the following code:

USE PUBS

GO

SELECT * FROM getAuthorsByState('CA')

As you can see, we're referring to our UDF as if it is a physical table. The result of our SELECT statement looks like this:



Notice how we've specified the value of the @state input parameter within quotes. If we wanted to, we could modify our UDF to also filter users based on their current contract settings:

USE PUBS

GO

ALTER FUNCTION getAuthorsByState

(

@state CHAR(2),

@contract BIT

)

RETURNS TABLE

AS

RETURN

(

SELECT au_fname + ' ' + au_lname AS aName

FROM authors

WHERE state = @state

AND contract = @contract

)

Notice how I've used the ALTER FUNCTION statement to change the contents of our inline-table valued UDF? UDF's can also be deleted from the database using the DROP FUNCTION statement in exactly the same way that we can use "DROP" to delete a database, a table, or a stored procedure.
blog comments powered by Disqus