"usp_ShoppingList" Stored Procedure

Description:

This stored procedure returns a recordset containing all the items in the given CartID. It is called whenever the Shopping Cart screen is displayed.

Definition:
CREATE Procedure usp_ShoppingList
(
    @CartID nvarchar(50),
	@Culture nvarchar(10)
)
AS



SELECT 
    P.ProductID, 
    P.[Name],
    P.ProductNumber,
    SCI.Quantity,
    dbo.ConvertCurrency(P.ListPrice, @Culture).ToString() as ListPrice,
    dbo.ConvertCurrency(P.ListPrice * SCI.Quantity, @Culture).ToString() as ExtendedAmount

FROM 
    Production.Product as P,
    Sales.ShoppingCartItem as SCI

WHERE 
    P.ProductID = SCI.ProductID
  AND 
    SCI.ShoppingCartID = @CartID

ORDER BY 
    P.[Name],
    P.ProductNumber;

Database Tables Used:

Product:  The Product table contains the core information about all of the items for sale on the Adventure Works Cycles web site. Its primary key is the ProductID identity field.

ShoppingCartItem:   The ShoppingCartItem table keeps track of the items a user has purchased.  Its primary key is the ShoppingCartItemID field.  The ShoppingCartID is a string which is used to identify the user who owns the basket of items.  There is a many to one relationship between the ShoppingCartItem table and the Product table.  Note that if not Quantity is supplied, a default of 1 is entered.