Creating User Defined Functions In SQL Server 2000

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 ]

BEGIN

function_body

RETURN

END

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:

RETURNS @myTable TABLE

(

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:

BEGIN

INSERT @myTable

SELECT au_id,

au_fname + ‘ ‘ + au_lname as au_name,

state

FROM dbo.authors

WHERE @contract = 1

END

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:



Enter and execute the following code in query analyzer:

USE PUBS

GO

CREATE FUNCTION getJobsByMinLvl

(

@minLvl TINYINT

)

RETURNS @jobTable TABLE

(

jobName VARCHAR(50),

difMinMax TINYINT

)

AS

BEGIN

INSERT @jobTable

SELECT job_desc,

(max_lvl - min_lvl)

FROM jobs

WHERE min_lvl >= @minLvl

RETURN

END

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:

USE PUBS

GO

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:


blog comments powered by Disqus