Nested cursors for SQL Server stored procedures

The following is a stored procedure with 2 cursors for an order cancellation

SetAnsi_nulls onSetQuoted_identifier onGoALTER PROCEDURE [dbo].[Cancelorderbysystem] asBEGINDeclare    /*declaring Variables*/@Status varchar( -),--Status@TimeNow datetime,--Current Time@TradeID varchar( -),--Order Tracking number@GoodsID int,--Product ID@Num int,--Quantity@SkuID int  --Specification IDSet @Status='Trade_closed_by_system'   /*Assigning a value to a variable*/Set @TimeNow=getdate() begin Transaction;--start the execution of a transaction--query all orders that have expired--determine if the order has a specification for an expired order by querying its sub-order--If there is no specification need to determine whether there are specifications, there is no change in the total inventory--If there is a specification first to judge the release of the specification, there is only release spec stock and total inventory--Modify the status of an orderDeclareCancelorder_cursorcursor  for  --declaring CursorsSelectTradeID fromWxtradewhereStatus='Wait_buyer_pay'  andOuttime<=@TimeNow OPENCancelorder_cursor--Open CursorFETCH NEXT  fromCancelorder_cursor--gets the next row of data for the cursor into @TradeID    --make the variable get the order number of the specified line of the current cursor ----------------------External cursor (master order) begin-------------------------- while(@ @FETCH_STATUS = 0)--FETCH statement execution succeededBEGIN  --Modify the status of an order  UpdateWxtradeSetStatus=@Status, Closetime=@TimeNow whereTradeID=@TradeID  --Modify the total inventory and spec inventory, and query its child orders for expired orders  DeclareOrderdetail_cursorcursor  for  --declaring Cursors  SelectGoodsid,skuid,num fromWxorderwhereTradeID=@TradeID  OPENOrderdetail_cursor--Open cursors (child orders)  FETCH NEXT  fromOrderdetail_cursor--gets the next row of data for the cursor   into @GoodsID,@SkuID,@Num   --causes the variable to get the item ID, specification ID, quantity of the line specified by the current cursor  ----------------------inner nested cursor (child order) begin----------------------  ------------------------------------------------------------------------   while(@ @FETCH_STATUS = 0)--FETCH statement execution succeeded  BEGIN    if(@SkuID  is NULL)    BEGIN      --no specifications need to first determine whether there are specifications, there is no change in the total inventory, no change      if  not exists(SelectSkuID from [Sku] whereGoodsid=@GoodsID)      BEGIN       UpdateGoodsSetQuantity=Quantity+@Num whereGoodsid=@GoodsID      END          END     Else    BEGIN      --If there is a specification first to determine whether the specification exists, the presence of the release of the specification inventory and total inventory      if exists(SelectSkuID from [Sku] whereSkuID=@SkuID)      BEGIN       Update [Sku] SetItemquantity=Itemquantity+@Num whereSkuID=@SkuID       UpdateGoodsSetQuantity=Quantity+@Num whereGoodsid=@GoodsID      END     END    FETCH NEXT  fromOrderdetail_cursor--get the next line of the cursor (sub-order)    into @GoodsID,@SkuID,@Num   --causes the variable to get the item ID, specification ID, quantity of the line specified by the current cursor  END  CLOSEOrderdetail_cursor--Close cursors (child orders)  deallocateOrderdetail_cursor--Release cursors (child orders) ----------------------inner nested cursor (child order) End---------------------- ---------------------------------------------------------------------- FETCH NEXT  fromCancelorder_cursor--get the next line of the cursor (main order)  into @TradeID    --make the variable get the order number of the specified line of the current cursorEndCLOSECancelorder_cursor--Close cursor (main order)deallocateCancelorder_cursor--Release cursor (main order) ----------------------External cursor (main order) End--------------------------if(@ @error>0) begin    rollback Transaction    return 0 EndElse     begin    Commit Transaction    return 1 EndEND

Reference: http://blog.csdn.net/gxiangzi/article/details/6774786#0-qzone-1-7441-d020d2d2a4e8d1a374a433f596ad1440

http://blog.csdn.net/superhoy/article/details/7663542#0-qzone-1-15425-d020d2d2a4e8d1a374a433f596ad1440

Nested cursors for SQL Server stored procedures