A user-defined function is a saved routine that can
accept parameters and return a value based on programming logic. There
are two types of user-defined functions in SQL Server, scalar-valued
functions and table-valued functions. Table-valued functions are further
classified as inline functions or multi-statement functions.
1. Scalar-Valued Functions
You can create a scalar-valued function to return a single value of the data type that is defined following the RETURNS clause. The RETURNS clause is used in the function definition to mandate the data type used by the RETURN
statement in the body of the function. You cannot create a function
that returns a value of types text, ntext, image, cursor, or timestamp.
The example in Listing 1 uses a scalar-valued function to return a running balance of all the orders that were placed for a certain customer.
Example 1. Sample Scalar-Valued Function to Return a Running Balance
--Create sample tables CREATE TABLE CustOrders (OrderID int PRIMARY KEY, CustID int, InvoiceAmt float)
CREATE TABLE Customers (CustID int PRIMARY KEY, CustName varchar(50))
--Insert sample data INSERT INTO Customers VALUES (1,'Ken Simmons'),(2,'Sylvester Carstarphen')
INSERT INTO CustOrders VALUES (1,1,500.25),(2,1,124.73),(3,1,42.86),(4,2,120.80),(5,2,12.74) GO
--Create function to return running balance CREATE FUNCTION dbo.udf_RunningSalesBalance (@CustID int, @OrderID int) RETURNS float AS BEGIN RETURN (SELECT SUM(InvoiceAmt) FROM CustOrders WHERE CustID = @CustID OrderID <= @OrderID) END
GO
--Query the new function SELECT OrderID, CustName, InvoiceAmt, dbo.udf_RunningSalesBalance(Customers.CustID, OrderID) RunningSales FROM Customers JOIN CustOrders ON Customers.CustID = CustOrders.CustID
|
You can see the results returned from querying the scalar function from Listing 1 in Figure 1.
As you can see in the RunningSales column, by passing in the CustID and
the OrderID to the scalar function, you can do a SUM on the InvoiceAmt
where the CustID equals the CustID, and the OrderID is less than or
equal to the OrderID. This calculation is performed for each row in
order to return a running balance.
2. Table-Valued Functions
Table-valued functions can
be used to provide similar functionality to views and stored procedures,
but table-valued functions provide some interesting benefits. Like a
view, an inline table-valued function returns a result set based on a
single query; but, inline table-valued functions can accept parameters
and views cannot. Multi-statement table-valued functions can be used to
encapsulate programming logic like stored procedures, but unlike stored
procedures, you can reference multi-statement table-valued functions in
the FROM clause of a SQL statement.
The following example in Listing 2 shows how to create an inline table-valued function that can accept parameters and be used in the FROM clause of a SQL statement. The function uses the Customers and CustOrders tables that were created in Listing 1.
Example 2. Syntax to Create an Inline Table-Valued Function
CREATE FUNCTION dbo.udf_TotalSalesByCustomer (@CustID int) RETURNS TABLE AS RETURN ( SELECT Customers.CustID, CustName, SUM(InvoiceAmt) TotalSales FROM Customers JOIN CustOrders ON Customers.CustID = CustOrders.CustID WHERE Customers.CustID = @CustID GROUP BY Customers.CustID, CustName ) GO
--Query the inline table-valued function with CustID 1 SELECT A.CustID, A.CustName, CustOrders.OrderID, CustOrders.InvoiceAmt, A.TotalSales FROM dbo.udf_TotalSalesByCustomer(1) A JOIN CustOrders ON A.CustID = CustOrders.CustID
|
You can see the results of Listing 2 in Figure 2.
By giving the function an alias (in this case A), you can reference the
results throughout the query. Being able to reference the output
returned by the function gives you the ability to do things such as JOIN the CustOrders table with the function and display the columns returned by the function in the SELECT statement.
The syntax for creating a
multi-statement table-valued function is much like creating a scalar
function, except you use a table variable to return a result set instead
of returning a single value. You need to define the table variable that
will be used to return the result set from a multi-statement
table-valued function following the RETURNS clause. The function in Listing 3
uses logic to check if the customer has ordered more than $500 worth of
merchandise. If so, a result set is returned applying a 10% discount to
all of the orders for that customer.
Example 3. Syntax Used to Create a Multi-Statement Table-Valued Function
CREATE FUNCTION dbo.udf_CustomerDiscount (@CustID int) RETURNS @CustDiscount TABLE (OrderID int, InvoiceAmt float, InvoiceAmtWithDiscount decimal(10,2)) AS BEGIN IF (SELECT SUM(InvoiceAmt) FROM CustOrders WHERE CustID = @CustID) > 500
BEGIN INSERT INTO @CustDiscount SELECT OrderID, InvoiceAmt, InvoiceAmt * .9 FROM CustOrders WHERE CustID = @CustID END ELSE BEGIN INSERT INTO @CustDiscount SELECT OrderID, InvoiceAmt, InvoiceAmt FROM CustOrders WHERE CustID = @CustID END
RETURN
END
GO
--Query the multi-statement table-valued function SELECT * FROM dbo.udf_CustomerDiscount(1)
|
You can view the results of Listing 3 in Figure 3.
You can see that since customer 1 has more than $500 worth of
merchandise, the discount is applied to the InvoiceAmtWith Discount
column. If you execute the same multi-statement table-valued function
for customer 2, the discount will not be reflected in the
InvoiceAmtWithDiscount column, since they have ordered less than $500
worth of merchandise.
To alter a function, you use the same syntax as creating a function, except you will need to change the CREATE keyword to ALTER. To drop an existing function, you need to issue the DROP FUNCTION statement followed by the function name, as shown in Listing 4.
Example 4. Syntax to Drop a User-Defined Function from the Database
DROP FUNCTION dbo.udf_CustomerDiscount