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.
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.
<span style="color: #008000;">-- Use the correct database
-- 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]
-- Create stub inline tvf
CREATE FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50))
( SELECT 'Stub Store' as Store, 'Stub City' as City );
-- Alter inline tvf
ALTER FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50))
s.Name AS 'Store',
a.City AS 'City'
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
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.
<span style="color: #008000;">-- Example use of the function
FROM [dbo].[ufnCustomersInRegion] ('Washington')
ORDER BY City;
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).