question

sarithasundarajan avatar image
sarithasundarajan asked

CTE alternative for below Cursor

declare @lc_acct_code as varchar(20) declare @lc_scr_code as varchar(20) declare @ld_exp_date as datetime declare @lc_series1 as varchar(10) declare @ln_sprice1 as numeric(11,4) declare @ln_check1_qty as int declare @ln_check1_uniqno as int declare @ln_check1_busirate as decimal(24,4)

declare @ln_check2_qty as int declare @ln_check2_uniqno as int declare @ln_check2_busirate as decimal(24,4)

declare @ln_basevalue as decimal(11,4) declare @ln_nettvalue as decimal(24,7)

declare @ZQty as int declare @LQty as int

declare check1 cursor for SELECT ACCT_CODE, SCRCODE ,expdate, qty ,uniqno,busirate,base_value FROM #TempBS where busi_type='B' ORDER BY acct_code, scrcode

OPEN check1 FETCH NEXT FROM check1 INTO @lc_acct_code,@lc_scr_code,@ld_exp_date,@ln_check1_qty,@ln_check1_uniqno, @ln_check1_busirate, @ln_basevalue

WHILE @@FETCH_STATUS = 0 BEGIN set @ZQty= @ln_check1_qty IF @ZQty >0 BEGIN print @lc_acct_code ------------------------------------------------------------------------------------------ declare check2 cursor for SELECT ACCT_CODE, SCRCODE ,expdate, qty ,uniqno,busirate,base_value FROM #TempBS where busi_type='S' and acct_code=@lc_acct_code and scrcode=@lc_scr_code and expdate=@ld_exp_date ORDER BY acct_code, scrcode,expdate

OPEN check2 FETCH NEXT FROM check2 INTO @lc_acct_code,@lc_scr_code ,@ld_exp_date,@ln_check2_qty,@ln_check2_uniqno, @ln_check2_busirate, @ln_basevalue

WHILE @@FETCH_STATUS = 0 BEGIN IF @ZQty>0 BEGIN IF @ln_check2_qty>0 BEGIN if @ln_check2_qty>@ZQty BEGIN set @LQty=@ZQty END else BEGIN set @LQty=@ln_check2_qty END

SET @ZQty=@ZQty-@LQty set @ln_check2_qty=@ln_check2_qty-@LQty

update #TempBS set qty=@ln_check2_qty where uniqno=@ln_check2_uniqno END END FETCH NEXT FROM check2 INTO @lc_acct_code,@lc_scr_code ,@ld_exp_date,@ln_check2_qty,@ln_check2_uniqno, @ln_check2_busirate, @ln_basevalue END close check2 deallocate check2 -- ------------------------------------------------------------------------------------------ END FETCH NEXT FROM check1 INTO @lc_acct_code,@lc_scr_code,@ld_exp_date,@ln_check1_qty,@ln_check1_uniqno, @ln_check1_busirate, @ln_basevalue

END

close check1 deallocate check1

sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.