UDF-quiz

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com

Do this quick pre-requisites:
    Download WideWorldImporters-Full.bak (database backup)
	Restore it to a SQL Server 2016 test instance (restore script below)
		https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
	
*****************************************************************************/


USE master;
GO

IF DB_ID('WideWorldImporters') IS NOT NULL
ALTER DATABASE WideWorldImporters SET OFFLINE WITH ROLLBACK IMMEDIATE

/* EDIT DRIVE/FOLDER LOCATIONS AS NEEDED */
RESTORE DATABASE WideWorldImporters FROM DISK=
	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
	WITH REPLACE,
	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
GO

USE WideWorldImporters;
GO

/* SCALAR FUNCTION */

CREATE FUNCTION Sales.CustomerName (@CustomerId INT)
RETURNS NVARCHAR(100)
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @CustomerName NVARCHAR(100);

    SELECT @CustomerName=CustomerName
    FROM Sales.Customers
    WHERE CustomerId=@CustomerId;

    RETURN(@CustomerName)
END;
GO

SELECT 
    Sales.CustomerName(CustomerID) as Customer,
    Sales.CustomerName(CustomerID) as CustomerRepeated,
    Sales.CustomerName(CustomerID) as CustomerRepeatedAgain,
    InvoiceDate
FROM Sales.Invoices
WHERE Sales.CustomerName(CustomerID) = N'Hoc Tran';
GO


/* TABLE VALUED FUNCTION WITH NC INDEX */

CREATE INDEX ix_CustomerID_INCLUDES on Sales.Invoices (CustomerID) INCLUDE (InvoiceDate);
GO

CREATE OR ALTER FUNCTION Sales.CustomerNameTVF (@CustomerId INT)
RETURNS @cust TABLE ( CustomerName NVARCHAR(100) )
AS
BEGIN 
    INSERT @cust 
        SELECT CustomerName
        FROM Sales.Customers
        WHERE CustomerId = @CustomerId;

        RETURN
END
GO

SELECT 
    CustomerName as Customer,
    CustomerName as CustomerRepeated,
    CustomerName as CustomerRepeatedAgain,
    InvoiceDate
FROM Sales.Invoices AS inv
CROSS APPLY Sales.CustomerNameTVF(inv.CustomerID) AS cn
WHERE CustomerName = N'Hoc Tran';
GO



/* TABLE VALUED FUNCTION WITH NESTED SCALAR FUNCTION*/

CREATE OR ALTER FUNCTION Sales.CustomerNameTVF2 (@CustomerId INT)
RETURNS TABLE 
AS
RETURN (
        SELECT Sales.CustomerName(@CustomerId) as CustomerName
)
GO

SELECT 
    CustomerName as Customer,
    CustomerName as CustomerRepeated,
    CustomerName as CustomerRepeatedAgain,
    InvoiceDate
FROM Sales.Invoices AS inv
CROSS APPLY Sales.CustomerNameTVF2(inv.CustomerID) AS cn
WHERE CustomerName = N'Hoc Tran';
GO