Procedure: Northwind.dbo.SalesByCategory

Collapse All Expand All

Procedure Properties

Name	Value
Owner	dbo
Creation Date	12/13/2004
Type	Standard T-SQL
Encrypted
ID	837578022
Implementation Type	Transact SQL

Creation options

Parameters

	Name	Description	DataType	Max Length	Type
	@CategoryName		nvarchar	15	Input
	@OrdYear		nvarchar	4	Input

Recordsets returned

	Name	DataType	Max Length
	ProductName	nvarchar	40
	TotalPurchase	decimal	17

Objects that depend on SalesByCategory

Objects that SalesByCategory depends on

	Object Name	Object Type	Dep Level
	Categories	Table	1
	[Order Details]	Table	1
	Orders	Table	1
	Products	Table	1
	Customers	Table	2
	Employees	Table	2
	Shippers	Table	2
	Suppliers	Table	2

Total 8 object(s)

Column Level Dependencies

	Object Name	Column	Object Type
	[Order Details]	Quantity	Table
	[Order Details]	Discount	Table
	[Order Details]	UnitPrice	Table
	[Order Details]	OrderID	Table
	[Order Details]	ProductID	Table
	Orders	OrderID	Table
	Orders	OrderDate	Table
	Products	ProductName	Table
	Products	ProductID	Table
	Products	CategoryID	Table
	Categories	CategoryID	Table
	Categories	CategoryName	Table

Total 12 column(s)

Graphical Dependencies

Permissions

Extended Properties

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SalesByCategory
    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 
BEGIN
    SELECT @OrdYear = '1998'
END

SELECT ProductName,
    TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
    AND OD.ProductID = P.ProductID 
    AND P.CategoryID = C.CategoryID
    AND C.CategoryName = @CategoryName
    AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
GO

See also

Buildmasters.com.au