/******************************************************
 *
 * Name:         table-value-function-inline.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-03-2013
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about inline table value functions (tvf).
 *
 ******************************************************/

-- 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


-- Example use of the function
SELECT *
FROM [dbo].[ufnCustomersInRegion] ('Washington')
ORDER BY City;
GO