Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 : Administering Database Objects - Working with Functions

7/2/2011 11:46:49 AM
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.

Figure 1. Result set returned by the scalar function created in Listing 1

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.

Figure 2. Result set returned by the inline table-valued function created in Listing 2

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.

Figure 3. Result set returned by the multistatement table-valued function created in Listing 3

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
Other -----------------
- SQL Server 2008 : Administering Database Objects - Working with Stored Procedures
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Disable a User Object
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Add User to Group
- Active Directory Domain Services 2008 : Manage Active Directory Domain Services Data - Move User Object
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Using the WCF-WSHttp Adapter
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Using Metadata Exchange
- BizTalk 2009 : Wcf Adapters: Standardizing Your Integration Endpoints - Securing Your Endpoints
- Microsoft PowerPoint 2010 : Adding a Digital Signature to a Macro Project & Assigning a Macro to a Toolbar or Ribbon
- Microsoft PowerPoint 2010 : Expanding PowerPoint Functionality - Controlling a Macro
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Configure Offline File Caching
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Share Folders
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Implement Permissions
- Microsoft Dynamics CRM 2011 : Working with Leads and Opportunities - Creating an Opportunity
- Microsoft Dynamics CRM 2011 : Qualifying a Lead & Disqualifying a Lead
- Microsoft Dynamics CRM 2011 : Understanding Leads and Opportunities & Creating a Lead and Tracking Lead Sources
- Performing an Exchange Server 2003 Installation
- Planning a Microsoft Exchange Server 2003 Infrastructure : Preparing Forests and Domains
- Planning a Microsoft Exchange Server 2003 Infrastructure : Installation Considerations
- SharePoint 2010 : Securing Information - Securing Sites
- SharePoint 2010 : Securing Information - Securing Site Collections
 
 
Most view of day
- Microsoft Excel 2010 : Calculating the Mode (part 4) - Getting the Mode of Categories with a Formula - Using Formula Evaluation
- Microsoft Systems Management Server 2003 : Security - Accounts and Groups
- Microsoft Dynamics AX 2009 : Form Customization (part 2) - Displaying an Image
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 2) - Finding Active Directory administration cmdlets
- Monitoring Windows Small Business Server 2011 : Using WSUS Reports
- Microsoft Exchange Server 2010 : Creating and Managing Accepted Domains (part 2) - Creating Accepted Domains
- Windows Server 2008 : Designing the Active Directory Administrative Model (part 2) - Using Group Strategy to Delegate Management Tasks
- Sharepoint 2013 : New Installation and Configuration - Managed Accounts
- Troubleshooting Stop Messages : Common Stop Messages (part 4)
- Microsoft Dynamic AX 2009 : Working with .NET Business Connector (part 2) - Exception Handling, Accessing Data
Top 10
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
- Microsoft Lync Server 2013 : Director Troubleshooting (part 2) - DNS Records, Logs
- Microsoft Lync Server 2013 : Director Troubleshooting (part 1) - Redirects, Certificates
- Microsoft Lync Server 2013 : Administration of the Director Role (part 4) - Services Management, Client Version Filter
- Microsoft Lync Server 2013 : Administration of the Director Role (part 3) - Topology Status
- Microsoft Lync Server 2013 : Administration of the Director Role (part 2) - Ports,Firewall Rules
- Microsoft Lync Server 2013 : Administration of the Director Role (part 1) - Services
- Microsoft Lync Server 2013 : Configuring the Director (part 2) - Web Services Ports,Reverse Proxy
- Microsoft Lync Server 2013 : Configuring the Director (part 1) - SRV Records, Web Services FQDN Overrides
- Sharepoint 2013 : SharePoint Designer 2013 (part 2) - Locking Down SharePoint Designer
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro