Functions.sql in SQL Server 2005

 

 

USE

Northwind;

--------------------------------------------

-- Scalar Functions

--------------------------------------------

CREATE

RETURNS int

BEGIN

DECLARE @Total AS int;

SELECT @Total = SUM(Quantity)

FROM dbo.[Order Details];

RETURN @Total;

END

GO

SELECT

dbo.fnProductsSold();

GO

SELECT

dbo.fnProductsSold() AS TotalSold

FROM

 

Products;

-- Scalar function with parameter

CREATE

(@ProductID int = null)

RETURNS int

WITH RETURNS NULL ON NULL INPUT

AS

BEGIN

DECLARE @total int;

SELECT @Total = SUM(Quantity)

FROM dbo.[Order Details]

WHERE ProductID = @ProductID;

RETURN @Total;

END

GO

SELECT

dbo.fnProductsSoldByProduct(NULL);

SELECT

dbo.fnProductsSoldByProduct(DEFAULT);

SELECT

dbo.fnProductsSoldByProduct(3);

GO

--------------------------------------------

-- Inline Table Valued Functions

--------------------------------------------

CREATE

RETURNS

AS

RETURN

SELECT

dbo.fnProductsSold() AS UnitsSold,

SUM(UnitPrice * Quantity) AS TotalSales,

AVG(UnitPrice) AS AveragePrice,

COUNT(*) AS LineItems

FROM dbo.[Order Details];

-- returns an error

SELECT

dbo.fnProductSelectStatistics();

-- correct calling syntax

SELECT

* FROM dbo.fnProductStatistics();

SELECT

FROM

dbo.fnProductStatistics();

-- Inline function with parameter

CREATE

(@ProductID int = NULL)

RETURNS

AS

RETURN

SELECT

dbo.fnProductsSoldByProduct(@ProductID)

AS UnitsSold,

SUM(UnitPrice * Quantity) AS TotalSales,

COUNT(*) AS LineItems

FROM dbo.[Order Details]

WHERE ProductID = @ProductID;

-- execute the function

SELECT

FROM fnStatisticsByProduct(4);

SELECT

* FROM dbo.fnStatisticsByProduct(DEFAULT);

SELECT

* FROM dbo.fnStatisticsByProduct(NULL);

-- Update from inline function

CREATE

(@ProductID int)

RETURNS

AS

RETURN

SELECT ProductID, ProductName, UnitPrice, UnitsInStock

FROM dbo.Products

WHERE ProductID = @ProductID

AND Discontinued = 0;

-- Execute as SELECT

SELECT

* FROM dbo.fnProductByID(3);

-- Update

UPDATE

dbo.fnProductByID(3)

SET

UnitsInStock = 63;

-- Display modified data

SELECT

FROM

dbo.fnProductByID(3);

--------------------------------------------

-- Multi-Statement Table Valued Functions

--------------------------------------------

-- get orders by employee or country

CREATE

(

@EmployeeID int = NULL,

@ShipCountry nvarchar(15) = NULL

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;

O.OrderID = T.OrderID
JOIN dbo.fnOrderTotal() AS T
dbo.fnOrdersByEmplyee_Country(NULL, 'USA') AS O
O.CustomerID, O.OrderID, T.Total
TABLE
dbo.Products
ProductID, ProductName, UnitPrice,
FUNCTION dbo.fnCalcDiscountPrice
JOIN dbo.Employees AS E dbo.fnOrdersByEmplyee_Country(NULL, 'Italy') AS O E.LastName, E.FirstName, O.CustomerID, O.OrderID DISTINCT ShipCountry CustomerID, EmployeeID, OrderID ShipCountry, CustomerID, OrderID ShipCountry, CustomerID, OrderID, EmployeeID

)

@Order TABLE

)

FUNCTION dbo.fnOrdersByEmplyee_Country ProductID, UnitsInStock

 

TABLE FUNCTION fnProductByID UnitsSold, TotalSales, LineItems TABLE FUNCTION dbo.fnStatisticsByProduct UnitsSold, TotalSales, AveragePrice, LineItems TABLE FUNCTION dbo.fnProductStatistics() FUNCTION dbo.fnProductsSoldByProduct AVG(UnitPrice) AS AvgPrice,

AS

FUNCTION dbo.fnProductsSold()

Comments

# re: Functions.sql in SQL Server 2005
Gravatar Good .sql script for functions. Kepp up the good work alright?
Left by Ken Waynne on 6/29/2006 6:54 PM
# re: Functions.sql in SQL Server 2005
Gravatar Great .sql scripting for functions in Server 2005. Thanks for sharing. I found you blog site is very interested and fun.
Left by Kathy London on 7/6/2006 4:03 PM
# re: Functions.sql in SQL Server 2005
Gravatar Thank you for sharing SQL Server 2005...
Left by Michael Connor on 7/6/2006 4:04 PM
# re: Functions.sql in SQL Server 2005
Gravatar I found this sample is ver useful when I applied to SQL Server 2000. Work on both SQL Server 2000/2005
Left by Diana Johnson on 7/6/2006 4:06 PM

Leave Your Comment

Title*
Name*
Email (never displayed)
 (will show your gravatar)
Url
Comment*

Please add 5 and 6 and type the answer here:

Preview Your Comment.