Cursor + recursive query client sub-client query financial credit

Use [crm01]go/****** object:storedprocedure [dbo].    [Account3yearstrade] Script date:07/15/2015 08:34:37 ******/set ansi_nulls ongoset QUOTED_IDENTIFIER ongo--Batch submitted through debugger: sqlquery1.sql|7|0| C:\users\crmqas2\appdata\local\temp\3\~vs3e13.sqlalter PROCEDURE [dbo]. [Account3yearstrade] as DECLARE @AccountId uniqueidentifier, @yt_total_price money, @OpportunityId uniqueidentifie R, @yt_invoice_date int, @yt_lastyeartrading money, @yt_toptwoannualturnover money, @yt_topthreeannualturn Over money, @nowdate INT--declares that the number of parameters in a cursor Mycursor,select statement must be the same as the variable name fetched from the cursor DECLARE mycursor cursorfor Select Acco Untid from Accountbase WHERE statecode=0 SELECT @nowdate =datepart (Year,getdate ())--Opens the cursor open mycursor--from the cursor Take out the data assignment to the 2 variables we just declared, FETCH NEXT from MyCursor into @AccountId--determine the state of the cursor--0 FETCH statement succeeds---1 FETCH statement fails or this row does not In the result set---2 rows that are fetched do not exist while (@ @fetch_status = 0) BEGIN Set @yt_lastyeartrading = 0set @yt_toPtwoannualturnover = 0set @yt_topthreeannualturnover = 0DECLARE mycursortwo cursorfor with Childaccount (accountid,pare Ntaccountid) as (SELECT Accountid,parentaccountid from Accountbase WHERE [email protected]union all SELECT A.acco Untid,a.parentaccountid from accountbase a,childaccount b where A.parentaccountid = B.accountid) Select Yt_total_price, DATEPART (year,yt_invoice_date), Opportunityid from Opportunitywhere yt_arrive in (SELECT AccountId from Childaccount) an D yt_status=100000002 Open Mycursortwo FETCH NEXT from Mycursortwo to @yt_total_price, @yt_invoice_date, @Opportunit Yidwhile (@ @fetch_status = 0) beginif (@[email protected]) Beginset @yt_lastyeartrading = @yt_lastyeartrading + IsNull (@yt_total_price, 0.0000) Endelse if (@[email protected]) beginset @yt_toptwoannualturnover = @yt_ Toptwoannualturnover + isnull (@yt_total_price, 0.0000) Endelse if (@[email protected]) Beginset @yt_ Topthreeannualturnover = @yt_topthreeannualturnover + isnull (@yt_total_price,0.0000) Endfetch NEXT from Mycursortwo to @yt_total_price, @yt_invoice_date, @OpportunityIdend CLOSE Mycursortwo deallocate mycursortwo Update account Set yt_lastyeartrading = @yt_lastyeartrading, Yt_lastyeartrading_ba SE = @yt_lastyeartrading, yt_toptwoannualturnover = @yt_toptwoannualturnover, yt_toptwoannualturnover_base = @yt_ Toptwoannualturnover,yt_topthreeannualturnover = @yt_topthreeannualturnover, yt_topthreeannualturnover_base = @yt_      Topthreeannualturnover where AccountId = @AccountId-use a cursor to remove a client FETCH next from the MyCursor into @AccountIdEND    --closing cursor close MyCursor--undo cursor Deallocate MyCursor

　　

Cursor + recursive query client sub-client query financial credit