Click here to Skip to main content
15,892,809 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Can anyone give suggestion what is the problem in while loop. Why it doesn't iterate?
SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[k_GetMedicineReportDatewiseStock]
	
    @fromdate datetime,
    @todate datetime,
    @mvtype varchar(50),
    @medicine varchar(50)
AS
declare
@squantity numeric(18,0),
@fquantity numeric(18,0),

 @date datetime
BEGIN
declare @i int
declare @cdate int
set @i = 0
while (@i < @cdate)

set  @cdate = (select DATEDIFF(dd,@fromdate, @todate)) 

begin

--set @date = @fromdate + 1
print '1'

print '2'
	
SET NOCOUNT ON;


set @squantity = (select sum(quantity)  from k_Master_MedicineInventory where type in (@mvtype)
 and medicinename in (@medicine) and date in (@date) )
print '3'
set @fquantity = (select sum(quantity)  from k_Master_FarmerMedicine_Disperse where type in (@mvtype)
 and medicinename in (@medicine) and date in (@date))

select MI.date,MI.quantity,MI.dcno,FD.quantity,(@squantity - @fquantity) as quantity,FD.farmcode,FD.remarks,MI.updatedby
from k_Master_MedicineInventory MI,k_Master_FarmerMedicine_Disperse FD  where MI.type in (@mvtype)
 and MI.medicinename in (@medicine) and MI.date in (@date) and FD.date in (@date) and FD.type in (@mvtype)
 and FD.medicinename in (@medicine)  order by MI.dcno

END

set @i = @i + 1
print '4'

end
Posted
Updated 3-May-12 2:10am
v2

1 solution

simple:
your code:
SQL
declare @i int
declare @cdate int
set @i = 0
while (@i < @cdate)


what is value of @cDate? 0! while 0 < 0 .....

something tells me that the line
SQL
set  @cdate = (select DATEDIFF(dd,@fromdate, @todate))
should be used before the while looop
 
Share this answer
 
v2
Comments
Member 8609405 3-May-12 8:16am    
thanks
Maciej Los 3-May-12 18:42pm    
The hawk eye ;)
Very good answer. My 5!

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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