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.
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:
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
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:
au_fname + + au_lname as au_name,
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:
Enter and execute the following code in query analyzer:
CREATE FUNCTION getJobsByMinLvl
RETURNS @jobTable TABLE
(max_lvl - min_lvl)
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 Ive 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: