Nested cursors for SQL Server stored procedures

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.