LISTING 2: Stored Procedure to Retrieve Package Information

CREATE PROC GetPackageInfo @Packageid INT
AS
SELECT Packages.PackageName, Packages.PackageDescription, Packages.PackageImage,
PackagePrice = (SUM((Products.ProductPrice * Products_Items.Quantity) * Items_Packages.Quantity) *.90 )
FROM Products
   INNER JOIN Products_Items ON Products.ProductId = Products_Items.ProductId
      INNER JOIN Items ON Products_Items.ItemId = Items.ItemId 
         INNER JOIN Items_Packages ON Items.Itemid = Items_Packages.Itemid
         AND Items.ItemIsAvailable = 1
            INNER JOIN Packages ON Items_Packages.PackageId = Packages.PackageId
            AND Packages.PackageId = @PackageId
GROUP BY Packages.PackageName, Packages.PackageDescription, Packages.PackageImage