Today, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value.
The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the former.
Like most Data Definition Language (DDL) constructs, a user define function has three operations associated with it: CREATE FUNCTION, ALTER FUNCTION and DROP FUNCTION
I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. The SQL snippet below, drops the function if it exists, creates a new stub function, and alters the function to be identical to the original.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<span style="color: #008000;">-- Use the correct database USE [AdventureWorks2012] GO -- Delete existing inline tvf IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnCustomersInRegion]') AND [type] in (N'IF') ) DROP FUNCTION [dbo].[ufnCustomersInRegion] GO -- Create stub inline tvf CREATE FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50)) RETURNS TABLE AS RETURN ( SELECT 'Stub Store' as Store, 'Stub City' as City ); GO -- Alter inline tvf ALTER FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50)) RETURNS TABLE AS RETURN ( SELECT DISTINCT s.Name AS 'Store', a.City AS 'City' FROM Sales.Store AS s INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE sp.Name = @Region ); GO </span> |
Please note that the return type of the function is a generic datatype of TABLE which is not defined. Also, there is only a single T-SQL statement in the function. In the Adventure Works example above, we are using a SELECT statement. This result set defines the return value.
While functions can be very complex, this simple one returns the customer store and city names for a given region. What good is a function, if it is not used in a query? The code snippet below returns the customer information for the Washington region.
|
1 2 3 4 5 6 7 |
<span style="color: #008000;">-- Example use of the function SELECT * FROM [dbo].[ufnCustomersInRegion] ('Washington') ORDER BY City; GO </span> |
In short, Inline Table Value Functions take zero or more values as an input parameters and return a table as single output parameter. They are limited by the fact that only one TSQL statement is supplied inside the RETURN statement.
Next time, I will be talking about Multiline Table Valued Functions (TVF).
