Browse DevX
Sign up for e-mail newsletters from DevX


Define Your Own Functions in SQL Server 2000-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 (@input_variables type) RETURNS data_type of result returned by function AS BEGIN ..... 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 NEXT_DAY).

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 Northwind go if exists(select * from sysobjects where
name = 'vw_totalsales') drop view vw_totalsales go create view vw_totalsales as (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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date