Properties

Owner: dbo 
Encrypted:  
Creation Date: 08/06/2000 
Description:  

Creation options

QUOTED_IDENTIFIER:  
ANSI_NULLS:  

Parameters

Name Direction DataType Length
  @CategoryName  INPUT  nvarchar  30 
  @OrdYear  INPUT  nvarchar 
Total: 2 parameter(s)

Objects that [dbo].[SalesByCategory] depends on

Object Name Owner Object Type Dep Level
  Categories  dbo  Table 
  Customers  dbo  Table 
  Employees  dbo  Table 
  Shippers  dbo  Table 
  Suppliers  dbo  Table 
  Orders  dbo  Table 
  Products  dbo  Table 
  Order Details  dbo  Table 
Total: 8 object(s)

Permissions

User/Role Select Insert Delete Update Execute DRI
  public          GRANT   
Total: 1 permission(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS 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
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of stored procedures