Happy Programming

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 3 - User Defined Functions
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Creating Scalar Functions

USE Tempdb
go
CREATE FUNCTION dbo.Multiply (@A INT, @B INT)
RETURNS INT
AS
BEGIN
RETURN @A * @B
End
go
SELECT dbo.Multiply (3,4)


USE ObxKites
SELECT dbo.fGetPrice('1006',GetDate(),DEFAULT)
SELECT dbo.fGetPrice('1001','5/1/2001',NULL)

---------------------------------------------------------
-- Working with Inline Table-Valued Functions


USE CHA2
go
CREATE FUNCTION fEventList ()
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID)

SELECT LastName, Code, DateBegin
FROM dbo.fEventList()


-- Parameters
USE OBXKites
go

-- View with Where clause
CREATE VIEW vPricelist
AS
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= GetDate())

SELECT *
FROM vPriceList
WHERE Code = '1001'

-- UDF with Parameters
CREATE FUNCTION dbo.fPriceList (
@Code CHAR(10) = Null, @PriceDate DateTime)
RETURNS Table
AS
RETURN(
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= @PriceDate)
AND (Code = @Code
OR @Code IS NULL) )

SELECT * FROM dbo.fPriceList(DEFAULT, '2/20/2002')

SELECT * FROM dbo.fPriceList('1001', '2/20/2002')

------------------------------------------------------
-- Using Multi-Statement Table-Valued UDFs

CREATE FUNCTION fPriceAvg()
RETURNS @Price TABLE
(Code CHAR(10),
EffectiveDate DATETIME,
Price MONEY)
AS
BEGIN
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, EffectiveDate, Price
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID

INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, Null, Avg(Price)
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID
GROUP BY Code
RETURN
END


SELECT *
FROM dbo.fPriceAvg()


---------------------------------------------------
-- Navigating a Hierarchy

Use Family

Select * from Person order by PersonID;

go
CREATE
-- alter
FUNCTION dbo.FamilyTree
(@PersonID CHAR(25))
RETURNS @Tree TABLE (PersonID INT, LastName VARCHAR(25), FirstName VARCHAR(25), Lv INT)
AS
BEGIN
DECLARE @LC INT
SET @LC = 1
-- insert the top level
INSERT @Tree
SELECT PersonID, LastName, FirstName, @LC
FROM dbo.Person with (NoLock)
WHERE PersonID = @PersonID

-- Loop through sub-levels
WHILE @@RowCount > 0
BEGIN
SET @LC = @LC + 1
-- insert the Class level
INSERT @Tree
SELECT Tree.PersonID, Tree.LastName, Tree.FirstName, @LC
FROM dbo.Person FamilyNode with (NoLock)
JOIN dbo.Person Tree with (NoLock)
ON FamilyNode.PersonID = Tree.MotherID
OR FamilyNode.PersonID = Tree.FatherID
JOIN @Tree CC
ON CC.PersonID = FamilyNode.PersonID
WHERE CC.Lv = @LC - 1
END
RETURN
END ;

-- end of function

Select * From dbo.FamilyTree(1);


-- Query With Recursive CTE (Common Table Expression)

WITH FamilyTree( LastName, FirstName, PersonID, lv)
AS (
-- Anchor
SELECT LastName, FirstName, PersonID, 1
FROM Person A
WHERE PersonID = 10

-- Recursive Call
UNION ALL
SELECT Node.LastName, Node.FirstName, Node.PersonID, lv + 1
FROM Person Node
JOIN FamilyTree ft
ON Node.MotherID = ft.PersonID
OR Node.FatherID = ft.PersonID
)
SELECT PersonID, LastName, FirstName, lv
FROM FamilyTree;

Select * From dbo.FamilyTree(1);


-----------------------------------------------------------------------
-- Having Fun with Apply

USE CHA2
go
CREATE FUNCTION fEventList2 (@CustomerID INT)
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
WHERE Customer.CustomerID = @CustomerID
)

SELECT C.LastName, Code, DateBegin, Tour
FROM Customer C
CROSS APPLY fEventList2(C.CustomerID)
ORDER BY C.LastName