Click here to Skip to main content
15,920,688 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
iam working on sql query
i have @Table_1,@Table_2 Variable
i fill @Table_1 then i need to fill @Table_2 depend on value in @Table_1
for eaxmple
SQL
@Table_1
(
ID bigint,
CategoryID bigint
CategoryType tinyint
)

@Table_2
(
QUantity float
TotalSales float
totalcost float
)

&& i have i table valued function (Fn_Table) that take the paremeter from @TAble_! to fill @Table2
like the following code
**************************
SQL
declare @Pks int
declare @Pk int
declare @CategoryID bigint

select @Pks=count(ID) from @Table_1
select @pK=1
WHILE(@Pks>0)
 begin
   select @CategoryID=CategoryID from @Table_1 where ID=pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  select @pks=@pks-1
  select @pk=@pk+1
 end

********************************
the query take long time & i think this is because ot the loop
so could anybody help me to get the same result without using loop

[edit]Code blocks added - OriginalGriff[/edit]
Posted
Updated 6-Jul-12 21:01pm
v2
Comments
AshishChaudha 7-Jul-12 3:11am    
could your show us you valued function fn_table???
[no name] 7-Jul-12 3:50am    
don't care about what happend in function fn_table because it's complicated & do many things

1 solution

SQL
declare @Pks int
declare @Pk int
declare @CategoryID bigint
 
set @Pks=count(ID) from @Table_1
set @pK=1
WHILE(@Pks>0)
 begin
   set @CategoryID=CategoryID from @Table_1 where ID=@pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  set @pks=@pks-1
  set @pk=@pk+1
 end
 
Share this answer
 
Comments
[no name] 7-Jul-12 6:25am    
where is the solution
i think this is the same code i put in the question
Vasim889 7-Jul-12 7:30am    
where is the exit loop in your sp. so process continously running b'cz not set the values to parameter .remove the select keywords to SET .it's working try tis one.
[no name] 7-Jul-12 7:40am    
it 'll not make any differnet if u say
set @pks=@pks-1
or
select @pks=@pks-1
it's the same
my question is i need to remove the loop & replace it with any more performance query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900