hose of you who program in Visual Basic, C, or other languages are probably used to writing your own functions. As a SQL programmer, you know how frustrating it’s been not to have this ability in previous versions of SQL Server.
In this 10-Minute Solution, I demonstrate how to create and utilize user-defined functions in SQL Server 2000.
The Limitations of Stored Procedures
Let’s first take a look at why you might want to create functions in SQL Server in the first place. After all, you have always been able to create stored procedures. Isn’t that enough?
Well, stored procedures have a very basic limitation; namely, you can’t use one as part of a
SELECT statement other than to set a variable. For example, if you had business logic that performed a calculation on an invoice, you could not have written the following code (until SQL Server 2000 came out):
select client, my_calc(invoice)from invoicesgroup by client
Similarly, you couldn’t join to the results of a stored procedure. Let’s say you had a procedure that identified your best customers and you wanted to pull the invoices for those customers to research their buying habits. The following code would have been the most convenient method, but until SQL Server 2000, it wouldn’t have been possible:
select client, invoice, amountfrom invoicesinner join my_best_customers() as won w.client = invoices.client
As I demonstrate, SQL Server 2000 allows you to do all of the above, and more.
How can I write a user-defined function in SQL Server 2000?
You can create a user-defined function in three ways:
- Create a scalar function to return a single value.
- Use an inline table function to return a table of results. However, you can’t have more than one SQL statement in the function.
- If your SQL is more complicated, you can use a multi-statement, table-valued function by defining the table that will be returned.
Perhaps the best way to start is by looking at a scalar function, the simplest type of function there is. This function returns a single data value. SQL Server has many built-in scalar functions: getdate, datepart, and substring, for example.
To create your own scalar function, use the
CREATE FUNCTION statement:
CREATE FUNCTION function_name(@input_variables type)RETURNS data_type of result returned by functionASBEGIN….. SQL Statements RETURN (data_value)END
Let’s demonstrate this statement by writing a function. Oracle has a useful function called
NEXT_DAY that computes the date of the next named day of the week (i.e., Sunday, Monday, etc.) after the given date. Go ahead and write your own function for SQL Server (see Listing 1 for the code to create a scalar function using
Inline Table-Valued Functions
Now create a function that returns a table. Use the Northwind database; it contains customers, orders, and order details. Suppose you want to analyze your customers by ranking them from best to worst. The function that you write will return the customerid, total sales, and rank of each customer in a table that can then be used by itself or joined within other tables. Furthermore, you will pass a parameter to the function to filter the customers based on the rank (e.g., top 3, top 10, etc.).
First, create a view in the Northwind database that summarizes the total sales for each customer, as follows:
use Northwindgo if exists(select * from sysobjects where
name = ‘vw_totalsales’) drop view vw_totalsalesgocreate view vw_totalsalesas(select CustomerID,sum (UnitPrice * Quantity *
1 – Discount) as ‘totalsales’from Orders o inner join [Order Details] odon (o.OrderID = od.OrderID)group by Customerid )
To find the rank of each customer, you need to find the number of distinct total sales values that are equal to or less than the customer in question. See Listing 2 for the code. Look more closely at the syntax you used. First of all, in this function you stated “returns table.” That tells SQL Server that the function is returning a table of results rather than a scalar value. But you didn’t define the table of results the function would be returning. Instead, you followed it with a single return statement that has one huge
SELECT statement in it.
The code you used could be improved. If you notice, the
SELECT COUNT() statement is repeated twice, once in the
SELECT statement to determine the rank and the second time in the
WHERE clause to filter which rows you want returned. For each of those statements, SQL Server has to traverse the view, which is itself a join between two tables. Instead, store the distinct sales values in a temporary table.
The major difference in the way that you define a multi-statement, table-valued function from the previous example is that you must declare the table that you will be returning.
Begin your improved version of the
GetCustByRank function with the following statement:
create function fn_GetCustByRankv2( @rank int)returns @GetCustByRank table( customerid nchar(5), totalsales money, rank int)asbegin
This function accepts a parameter called “rank” and returns a table variable called
@GetCustByRank that has three columns: id, total sales, and rank.
Now you can get on with performing the necessary calculations. Because you can now use multiple statements, use another table variable to store the distinct number of total sales:
declare @tsales table( totalsales money)insert into @tsalesselect distinct totalsales as ‘totalsales’
Then use the tsales table variable to perform your calculations and insert the results into the
@GetCustByRank table variable:
insert into @GetCustByRankselect s1.customerid,s1.totalsales,(select count
(distinct s2.totalsales)from @tsales s2wheres1.totalsales <= s2.totalsales)from vw_totalsales s1where(select count (distinct s2.totalsales)from @tsales s2wheres1.totalsales <= s2.totalsales) <= @rankreturnend
For the sake of clarity, see Listing 3 for the function in its entirety.
Referring to Functions
Now that you’ve created three functions, test them out. Because
fn_getnextdate() is a scalar function, you can simply type the following statement to find the Thursday after March 2, 2002:
Notice that the function name is qualified with the owner. This is unlike system-supplied functions that you can simply refer to by name?as in
SELECT GETDATE(). If you aren’t in the database in which you created the function, you will also need to qualify the function name with the database name. In my example, because I’ve created all the functions in the Northwind database, if I was in the pubs database, I would need to write the following SQL:
fn_GetCustByRankv2 functions both return tables, so they must be treated like tables. The proper way to execute them in a select statement is as follows:
select [whatever columns I’m interested in]from dbo.fn_GetCustByRank
select *from dbo.fn_GetCustByRank
If you use the wrong syntax, you will get a rather misleading error message. For example if I type
select dbo.fn_GetCustByRank (6), leaving out the columns that you want, you get the following error message: “Invalid object name ‘dbo.fn_GetCustByRank’.” In reality, the function exists; you just didn’t invoke it properly.