February 21, 2012 at 11:54 pm
hello all.
i have two while loop and i want to insert new rows in temp table in internal loop and finally i want to see all new row in external loop.but in my code,i can see last new rows but not all.
please help me taht how do i do?
IF OBJECT_ID('TempDB..#Out8','U') IS NOT NULL
DROP TABLE #Out8
create table #out8(ID bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,Tbl_customerid_fk bigint)
set @sql='select distinct Tbl_customerid_fk
from V_ACC_Add
where ACC_AdID_fk='+cast(@Acc_AdID as nvarchar(50))+' and ACC_AcCustomerNotice=1 and TBL_CustomerNotice=1
order by Tbl_customerid_fk
'
insert into #out8 exec sp_executesql @sql
set @count1=@@ROWCOUNT
set @j-2=1
set @SumAddDebit=0
set @SumAddCredit=0
while (@j<=@count1)
begin
select @Tbl_customerid_fk=Tbl_customerid_fk from #out8 where ID=@j
IF OBJECT_ID('TempDB..#Out9','U') IS NOT NULL
DROP TABLE #Out9
create table #out9(ID bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,ACC_AddDebit numeric(18,0),ACC_AddCredit numeric(18,0),PageNO int,SumDocument100 nvarchar(max),SumDocument200 nvarchar(max),Type int,ACC_AddDescription nvarchar(max),TamrkozName nvarchar(1000),SubTamrkozName nvarchar(1000),TBL_CustomerID_fk bigint)
set @sql='select ACC_AddDebit ,ACC_AddCredit, 0 as PageNO ,'''' as SumDocument100,'''' as SumDocument200,1 as Type,ACC_AddDescription,TamrkozName,SubTamrkozName,TBL_CustomerID_fk
from V_ACC_Add
where ACC_AdID_fk='+cast(@Acc_AdID as nvarchar(50))+' and ACC_AcCustomerNotice=1 and TBL_CustomerNotice=1 and TBL_CustomerID_fk='+cast(@Tbl_customerid_fk as nvarchar(50))+'
order by Tbl_customerid_fk'
insert into #out9 exec sp_executesql @sql
set @count2=@@ROWCOUNT
set @i=1
while (@i<=@count2)
begin
select @ACC_AddDebit=ACC_AddDebit,@ACC_AddCredit=ACC_AddCredit from #out9 where ID=@i
set @sql='update #Out9
set PageNO='+cast(((@i/12)+1) as nvarchar(20))+'
where ID='+cast(@i as nvarchar(20))+'
'
exec sp_executesql @sql
set @SumAddDebit=@SumAddDebit+@ACC_AddDebit
set @SumAddCredit=@SumAddCredit+@ACC_AddCredit
set @i=@i+1
end
set @EndCounter=11-(@count2-(@count2/12)*11)
if (@EndCounter < 0)
begin
set @EndCounter = 11 + @EndCounter;
end
set @i=1
IF OBJECT_ID('TempDB..#Out20','U') IS NOT NULL
DROP TABLE #Out20
create table #out20(ID bigint NOT NULL PRIMARY KEY,ACC_AddDebit numeric(18,0),ACC_AddCredit numeric(18,0),PageNO int,SumDocument100 nvarchar(max),SumDocument200 nvarchar(max),Type int,ACC_AddDescription nvarchar(max),TamrkozName nvarchar(1000),SubTamrkozName nvarchar(1000),TBL_CustomerID_fk bigint)
while(@i<=@EndCounter)
begin
insert into #out9 values(0,0,((@count2/12)+1),dbo.FXGLB_NumberToWords(@SumAddDebit,1),dbo.FXGLB_NumberToWords(@SumAddCredit,1),1,'---','-','-',@Tbl_customerid_fk)
set @i=@i+1
end
insert into #Out20
select * from #out9
set @j-2=@j+1
end
select * from #Out20
February 22, 2012 at 12:43 am
1) Lose the dynamic sql as this query sequence really doesn't need it !
2) collect your data using a join / group by combination ! Think set based in stead of procedural/cursor based.
3) there is no need to code a "order by" in the select used with an insert unless the select statement holds a top clause. ( if that would be needed, the engine would handle it. )
4) have a look at the wonderful article Jeff Moden wrote about "Solving the Running Total and Ordinal Rank Problems"[/url]. http://www.sqlservercentral.com/articles/T-SQL/68467/
So you get supported using the appropriate windowing functions to accomplish you quest.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply