Creating a Scalar Function
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
RETURNS data_type of result returned by function
..... SQL Statements
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:
if exists(select * from sysobjects where
name = 'vw_totalsales')
drop view vw_totalsales
create view vw_totalsales
(select CustomerID,sum (UnitPrice * Quantity *
1 - Discount) as 'totalsales'
from Orders o
inner join [Order Details] od
on (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.