Store process learning for shopping carts

Stored Procedures | Shopping Cart
Never touched a stored procedure and wrote several stored procedures for this project. Just as learning, oh, this shopping cart stored procedures used table (table), Cursor (cursor) variables.

Requirements are as follows:

A product table [Ware], which contains fields including Id,marketprice,memberprice,vipprice, and so on, also a member shopping cart [Membercar] datasheet. Included are Id,memberid,wareid,warenum and so on. The requirement is to be able to obtain all the data in the shopping cart table and the total price of marketprice*warenum (all goods and), that is, n items to get the total price of these n items rather than a single price. Originally in the client directly according to the shopping cart [membercar] Table data read the product table [Ware] in the data, and then add to the variable. But the problem is that all the items in the shopping cart are displayed using a paging display. So when the accumulation of problems, if in order to accumulate these prices in the paging cycle and then add a cycle of all records to accumulate price data, this is not not, but the efficiency can be imagined!!!

One solution is to reconstruct a mirror of a shopping cart table, but it has a number of price totals for a product. i.e. Marketprice*warenum (single commodity). Reading the cart instead of reading the original cart datasheet, reading the mirrored datasheet and accumulating the total price at the time of reading.

The stored procedure code is as follows:
----#####################################################################################
----# Process function: Returns all the data in the user's shopping cart, and contains statistics on the price of the goods (Price = number of items on the list * Shopping cart table)
----# Author: The fish that died in the water
----# Date: October 15, 2004
----#####################################################################################
CREATE PROCEDURE membercar_info @MemberID INT as
--Use this sentence to maintain the client's data
SET NOCOUNT on
DECLARE @Car_ID int, @Car_WareID int, @Car_WareNum smallINt
DECLARE @Car_TotalMemberPrice Numeric (8,2), @Car_TotalMarketPrice Numeric (8,2), @Car_TotalVipPrice Numeric (8,2)
--Defines a cursor variable that queries the Membercar table. Get Data
DECLARE Car_cursor Cursor for
Select id,wareid,warenum from [Membercar] Where memberid= @MemberID and ispay=0
--Create a temporary table variable
DECLARE @Car_Table Table (ID INT,
Wareid int,warenum smallint,marketprice Numeric (8,2), Memberprice Numeric (8,2), Vipprice Numeric (8,2))
--Open the Membercar table to extract the data to the temporary variable
OPEN Car_cursor
FETCH NEXT from Car_cursor into @Car_ID, @Car_WareID, @Car_WareNum
While @ @FETCH_STATUS = 0
BEGIN
--Obtain the total market price, the total member price and the total VIP price of the goods
Select @Car_TotalMarketPrice =marketprice* @Car_WareNum, @Car_TotalMemberPrice =memberprice* @Car_WareNum, @Car_ totalvipprice=vipprice* @Car_WareNum from [Ware] Where id= @Car_WareID
--Add to temporary variable table
INSERT into @Car_Table (id,wareid,warenum,marketprice,memberprice,vipprice) Values (@Car_ID, @Car_WareID, @Car_ Warenum, @Car_TotalMarketPrice, @Car_TotalMemberPrice, @Car_TotalVipPrice)
--No to the end of the table, continue to extract the data to the temporary variable
FETCH NEXT from Car_cursor into @Car_ID, @Car_WareID, @Car_WareNum
End
--Close the Membercar database cursor
Close Car_cursor
Deallocate car_cursor
--Returns the final data table
SELECT * from @Car_Table
Go



Ask the expression is not very clear, everybody forgives ...