Never exposed the stored procedure, several stored procedures were written for this project. Just study, huh, huh, use Table (Table), CURSOR (Cursor) variable during the stored process.
Requirements are as follows:
A product list [Ware], which contains the fields with ID, Marketprice, MemberPrice, Vipprice, etc., and a member shopping cart [MemberCar] data sheet. Included with ID, MemberID, Wareid, Warenum, etc. The current requirements are the total price of all the data and MarketPrice * Warenum (all goods, and) in the shopping counter, that is, n-containing goods get the total price of this N item rather than a single price. Originally reads the data in the product table [Ware] according to the data of the shopping cart [MEMBERCAR] table according to the data of the shopping cart, it can be accumulated. But the problem is, all the items in the shopping cart use the paging display. So when you are accumulating, you will be able to accumulate the price data when you add a loop to all records in the paging cycle display in order to accumulate these prices, which is not good, but the efficiency is imagine! ! !
The current solution is to re-construct a mirror of a shopping cart, but there are several prices of goods. That is, MarketPrice * Warenum (single product). Thereby reading the shopping cart is not reading the original shopping cart data table, but reading this mirror data table and is increasing in reading.
The stored procedure code is as follows: ---- ############################################################################################################################################################################################################################################################################################ ############################## ---- # process role: returns all user data of the shopping cart, and includes statistics (number of commodity price = price * table cart table of) the price of goods ---- #: dead fish in the water ---- # Date: October 15, 2004 ---- ################################################################################################################################ ######################################### c c @MemberID iNT AS-- use this sentence to maintain client data SET NOCOUNT ONDECLARE @Car_ID int, @ Car_WareID int, @ Car_WareNum smallINtDECLARE @Car_TotalMemberPrice Numeric (8,2), @ Car_TotalMarketPrice Numeric (8,2), @ Car_TotalVipPrice Numeric ( 8, 2) - Define a cursor variable in Query the Membercar table.
Acquiring 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 , MEMBERPRICE NUMERIC (8, 2) - get the total commodity market price, total price and total VIP membership price Select @ Car_TotalMarketPrice = MarketPrice * @ Car_WareNum, @ Car_TotalMemberPrice = MemberPrice * @ Car_WareNum, @ Car_TotalVipPrice = VipPrice * @ Car_WareNum From [Ware] Where ID = @ Car_WareID - added to the 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) - not to the end of the list, continues Extract data to temporary variable fetch next from car_cursor @ car_id, @ car_wareid, @ car_wareenumend - Close MEMBERCAR database Click CLOSE CAR_CURSORDEAllocate Car_cu RSOR - Returns the final data table Select * from @car_tableGo requires expression not very clear, everyone is forgiven ...
