createdbobjects.sql

电子商务入门套件是一个电子商务网络店面

/* First, return the OrderTotal out param */
SELECT  
    @OrderTotal = Cast(SUM(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as money)
    
FROM    
    CMRC_OrderDetails
    
WHERE   
    OrderID= @OrderID

/* Then, return the recordset of info */
SELECT  
    CMRC_Products.ProductID, 
    CMRC_Products.ModelName,
    CMRC_Products.ModelNumber,
    CMRC_OrderDetails.UnitCost,
    CMRC_OrderDetails.Quantity,
    (CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as ExtendedAmount

FROM
    CMRC_OrderDetails
  INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID = CMRC_Products.ProductID
  
WHERE   
    OrderID = @OrderID

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure CMRC_OrdersList
(
    @CustomerID int
)
As

SELECT  
    CMRC_Orders.OrderID,
    Cast(sum(CMRC_OrderDetails.Quantity*CMRC_OrderDetails.UnitCost) as money) as OrderTotal,
    CMRC_Orders.OrderDate, 
    CMRC_Orders.ShipDate

FROM    
    CMRC_Orders 
  INNER JOIN CMRC_OrderDetails ON CMRC_Orders.OrderID = CMRC_OrderDetails.OrderID

GROUP BY    
    CustomerID, 
    CMRC_Orders.OrderID, 
    CMRC_Orders.OrderDate, 
    CMRC_Orders.ShipDate
HAVING  
    CMRC_Orders.CustomerID = @CustomerID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ProductCategoryList

AS

SELECT 
    CategoryID,
    CategoryName

FROM 
    CMRC_Categories

ORDER BY 
    CategoryName ASC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ProductDetail
(
    @ProductID    int,
    @ModelNumber  nvarchar(50) OUTPUT,
    @ModelName    nvarchar(50) OUTPUT,
    @ProductImage nvarchar(50) OUTPUT,
    @UnitCost     money OUTPUT,
    @Description  nvarchar(4000) OUTPUT
)
AS

SELECT 
    @ProductID    = ProductID,
    @ModelNumber  = ModelNumber,
    @ModelName    = ModelName,
    @ProductImage = ProductImage,
    @UnitCost     = UnitCost,
    @Description  = Description

FROM 
    CMRC_Products

WHERE 
    ProductID = @ProductID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ProductsByCategory
(
    @CategoryID int
)
AS

SELECT 
    ProductID,
    ModelName,
    UnitCost, 
    ProductImage

FROM 
    CMRC_Products

WHERE 
    CategoryID = @CategoryID

ORDER BY 
    ModelName, 
    ModelNumber


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ProductsMostPopular

AS

SELECT TOP 5 
    CMRC_OrderDetails.ProductID, 
    SUM(CMRC_OrderDetails.Quantity) as TotalNum, 
    CMRC_Products.ModelName
    
FROM    
    CMRC_OrderDetails
  INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID = CMRC_Products.ProductID
  
GROUP BY 
    CMRC_OrderDetails.ProductID, 
    CMRC_Products.ModelName
    
ORDER BY 
    TotalNum DESC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ProductSearch
(
    @Search nvarchar(255)
)
AS

SELECT 
    ProductID,
    ModelName,
    ModelNumber,
    UnitCost, 
    ProductImage

FROM 
    CMRC_Products

WHERE 
    ModelNumber LIKE '%' + @Search + '%' 
   OR
    ModelName LIKE '%' + @Search + '%'
   OR
    Description LIKE '%' + @Search + '%'

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ReviewsAdd
(
    @ProductID     int,
    @CustomerName  nvarchar(50),
    @CustomerEmail nvarchar(50),
    @Rating        int,
    @Comments      nvarchar(3850),
    @ReviewID      int OUTPUT
)
AS

INSERT INTO CMRC_Reviews
(
    ProductID, 
    CustomerName, 
    CustomerEmail, 
    Rating, 
    Comments
)
VALUES
(
    @ProductID, 
    @CustomerName, 
    @CustomerEmail, 
    @Rating, 
    @Comments
)

SELECT 
    @ReviewID = @@Identity


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ReviewsList
(
    @ProductID int
)
AS

SELECT 
    ReviewID, 
    CustomerName, 
    Rating, 
    Comments
    
FROM 
    CMRC_Reviews
    
WHERE 
    ProductID = @ProductID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int
)
As

DECLARE @CountItems int

SELECT
    @CountItems = Count(ProductID)
FROM
    CMRC_ShoppingCart
WHERE
    ProductID = @ProductID
  AND
    CartID = @CartID

IF @CountItems > 0  /* There are items - update the current quantity */

    UPDATE
        CMRC_ShoppingCart
    SET
        Quantity = (@Quantity + CMRC_ShoppingCart.Quantity)
    WHERE
        ProductID = @ProductID
      AND
        CartID = @CartID

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO CMRC_ShoppingCart
    (
        CartID,
        Quantity,
        ProductID
    )
    VALUES
    (
        @CartID,
        @Quantity,
        @ProductID
    )


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartItemCount
(
    @CartID    nvarchar(50),
    @ItemCount int OUTPUT
)
AS

SELECT 
    @ItemCount = COUNT(ProductID)
    
FROM 
    CMRC_ShoppingCart
    
WHERE 
    CartID = @CartID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartList
(
    @CartID nvarchar(50)
)
AS

SELECT 
    CMRC_Products.ProductID, 
    CMRC_Products.ModelName,
    CMRC_Products.ModelNumber,
    CMRC_ShoppingCart.Quantity,
    CMRC_Products.UnitCost,
    Cast((CMRC_Products.UnitCost * CMRC_ShoppingCart.Quantity) as money) as ExtendedAmount

FROM 
    CMRC_Products,
    CMRC_ShoppingCart

WHERE 
    CMRC_Products.ProductID = CMRC_ShoppingCart.ProductID
  AND 
    CMRC_ShoppingCart.CartID = @CartID

ORDER BY 
    CMRC_Products.ModelName, 
    CMRC_Products.ModelNumber

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartMigrate
(
    @OriginalCartId nvarchar(50),
    @NewCartId      nvarchar(50)
)
AS

UPDATE 
    CMRC_ShoppingCart
    
SET 
    CartID = @NewCartId 
    
WHERE 
    CartID = @OriginalCartId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartRemoveAbandoned

AS

DELETE FROM CMRC_ShoppingCart

WHERE 
    DATEDIFF(dd, DateCreated, GetDate()) > 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartRemoveItem
(
    @CartID nvarchar(50),
    @ProductID int
)
AS

DELETE FROM CMRC_ShoppingCart

WHERE 
    CartID = @CartID
  AND
    ProductID = @ProductID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartTotal
(
    @CartID    nvarchar(50),
    @TotalCost money OUTPUT
)
AS

SELECT 
    @TotalCost = SUM(CMRC_Products.UnitCost * CMRC_ShoppingCart.Quantity)

FROM 
    CMRC_ShoppingCart,
    CMRC_Products

WHERE
    CMRC_ShoppingCart.CartID = @CartID
  AND
    CMRC_Products.ProductID = CMRC_ShoppingCart.ProductID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE Procedure CMRC_ShoppingCartUpdate
(
    @CartID    nvarchar(50),
    @ProductID int,
    @Quantity  int
)
AS

UPDATE CMRC_ShoppingCart

SET 
    Quantity = @Quantity

WHERE 
    CartID = @CartID 
  AND 
    ProductID = @ProductID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO