RETURNS
(
ShipCountry nvarchar(15) NOT NULL,
CustomerID nchar(5) NOT NULL,
OrderID int NOT NULL,
EmployeeID int NOT NULL
AS
BEGIN
-- If all inputs null, return all rows.
-- Sort by ShipCountry, CustomerID, OrderID, EmployeeID
IF @EmployeeID IS NULL AND @ShipCountry IS NULL
INSERT @Order
SELECT ShipCountry, CustomerID, OrderID, EmployeeID
FROM dbo.Orders
ORDER BY ShipCountry, CustomerID, OrderID, EmployeeID;
ELSE IF @EmployeeID IS NULL AND @ShipCountry IS NOT NULL
-- Filter by ShipCountry.
-- Sort by EmployeeID, CustomerID, OrderID
INSERT @Order
SELECT ShipCountry, CustomerID, OrderID, EmployeeID
FROM dbo.Orders
WHERE ShipCountry=@ShipCountry
ORDER BY EmployeeID, CustomerID, OrderID;
ELSE IF @EmployeeID IS NOT NULL AND @ShipCountry IS NULL
-- Filter by EmployeeID.
-- Sort by ShipCountry, CustomerID, OrderID
INSERT @Order
SELECT ShipCountry, CustomerID, OrderID, EmployeeID
FROM dbo.Orders
WHERE EmployeeID=@EmployeeID
ORDER BY ShipCountry, CustomerID, OrderID;
ELSE IF @EmployeeID IS NOT NULL AND @ShipCountry IS NOT NULL
-- Filter by ShipCountry and EmployeeID.
-- Sort by ShipCountry, CustomerID, OrderID, EmployeeID
INSERT @Order
SELECT ShipCountry, CustomerID, OrderID, EmployeeID
FROM dbo.Orders
WHERE ShipCountry=@ShipCountry AND EmployeeID=@EmployeeID
ORDER BY ShipCountry, CustomerID, OrderID, EmployeeID;
RETURN
END
;
GO
-- Execute the function
DECLARE
@EmployeeID nchar(5);
DECLARE
@ShipCountry nvarchar(15)
SET
@EmployeeID = NULL
SET
@ShipCountry = NULL
SELECT
FROM
dbo.fnOrdersByEmplyee_Country(@EmployeeID, @ShipCountry)
GO
-- Filter on employee
SELECT
FROM
dbo.fnOrdersByEmplyee_Country(6, NULL);
GO
-- Rearrange column and sort orders
SELECT
FROM
dbo.fnOrdersByEmplyee_Country(NULL, 'Spain')
ORDER
BY CustomerID, EmployeeID, OrderID;
GO
-- List distinct countries
SELECT
FROM
dbo.fnOrdersByEmplyee_Country(6, NULL);
GO
-- Joining functions to tables
SELECT
FROM
INNER
ON
O.EmployeeID = E.EmployeeID
ORDER
BY E.LastName;
---------------------------------------------------
-- Using Functions
---------------------------------------------------
-- Create a scalar function to calculate a discount
CREATE
(
@price money,
@discount int
)
RETURNS money
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @work money
SELECT @work = @price - (@price * (@discount * .01))
RETURN @work
END
;
-- Execute the function against the Products table
SELECT
dbo.fnCalcDiscountPrice(UnitPrice,5) AS [5% Off],
dbo.fnCalcDiscountPrice(UnitPrice,3) AS [3% Off]
FROM
ORDER
BY UnitPrice DESC;
-- Using Table-Valued Functions in a subquery
-- Compute order total
IF
Object_id('dbo.fnOrderTotal') IS NOT NULL
DROP FUNCTION dbo.fnOrderTotal;
GO
CREATE
FUNCTION dbo.fnOrderTotal()
RETURNS
AS
RETURN SELECT
OrderID, SUM(UnitPrice*Quantity) AS Total
FROM dbo.[Order Details]
GROUP BY OrderID
GO
-- Using functions in subqueries
SELECT
FROM
INNER
ON
WHERE
T.Total >
(SELECT AVG(Total) FROM dbo.fnOrderTotal())
ORDER
BY CustomerID, Total DESC;
-- Cleanup
IF
Object_id('dbo.fnProductsSold') IS NOT NULL
DROP FUNCTION dbo.fnProductsSold;
IF
Object_id('dbo.fnProductsSoldByProduct') IS NOT NULL
DROP FUNCTION dbo.fnProductsSoldByProduct;
IF
Object_id('dbo.fnProductStatistics') IS NOT NULL
DROP FUNCTION dbo.fnProductStatistics;
IF
Object_id('dbo.fnStatisticsByProduct') IS NOT NULL
DROP FUNCTION dbo.fnStatisticsByProduct;
IF
Object_id('dbo.fnProductByID') IS NOT NULL
DROP FUNCTION dbo.fnProductByID;
IF
OBJECT_ID (N'dbo.fnOrdersByEmplyee_Country') IS NOT NULL
DROP FUNCTION dbo.fnOrdersByEmplyee_Country;
IF
Object_id('dbo.fnCalcDiscountPrice') IS NOT NULL
DROP FUNCTION dbo.fnCalcDiscountPrice;
IF
Object_id('dbo.fnOrderTotal') IS NOT NULL
DROP FUNCTION dbo.fnOrderTotal;