While @counter <= @maxcounter

[tjb2]

I have a while construct that looks something like this:

While @counter<= @maxcounter

do some stuff

This is adding one more record than the actual value of @maxcounter. At the end of processing I add 1 to the @counter variable. So for example if @maxcounteris = 7 the stored procedure returns as if @maxcounter was = 8...

Any thoughts

TJ

 

[ClaireHCM]

I dont really get u.Can you explain little bit the required functionality
why if @maxcounteris = 7 the stored procedure returns as if @maxcounter was = 8...

 

[tjb2]

I thought maybe I was being a little vague. here's the entire stored proc; please be warned I inherited this beast and I am tring to add to it:


CREATE PROCEDURE web_grph_AvgWkSls(@UntNum char(3),@EndYear char(4),@numYears char(2)) AS

BEGIN
declare @PrStart datetime
declare @PrEnd datetime
declare @WksPr integer
declare @period integer
declare @user varchar(32),
@regid int
declare @AuditedDt datetime
declare @WeekToUse datetime
declare @Weeks integer
declare @WorkingWk datetime
--***********************************
declare @maxperdate datetime
declare @maxyears char(2)
declare @maxperiod integer
declare @pers integer
set @maxyears = @numyears
--***********************************

select @user = suser_sname()
select @regid = limit_to_regid from tblSalesSecurity where users_name = @user


If @numYears > 0
begin
Create Table #output
(UntNum integer not null,
Description varchar(30) not null,
Sls_Year integer not null,
Period integer null,
Avg_Wk_sales integer null)

if (@regid = 0) or exists(select 1 from tblstaffing where untnum = @untnum and personid = @regid)
begin
While @numYears > 0
begin
if @numyears = @maxyears
begin
set @maxperdate = (select max(perdate) from performancegrid where datepart(yy,perdate) = @endyear)
exec getcompanyperiod @maxperdate, '' ,@maxperiod output
end
else
set @maxperiod = 12

Select @period = 1
While @period <= @maxperiod
begin
Execute GetPeriodDates @EndYear,@period,@PrStart output,@PrEnd output
select @WksPr = datediff(wk,@PrStart,@PrEnd)

select @AuditedDt = datDateSetting from tblSystemSettings where txtSettingName = 'DateOfMostRecentSalesData_DD'
select @WeekToUse = @PrEnd
if @AuditedDt >= @PrStart and @AuditedDt <= @PrEnd
begin
select @Weeks = @WksPr
select @WorkingWk = @PrEnd
while @Weeks > 0
begin
if @AuditedDt > dateadd(wk,-1,@WorkingWk) and @AuditedDt < @WorkingWk
begin
select @WeekToUse = dateadd(wk,-1,@WorkingWk)
end
select @Weeks = @Weeks - 1
select @WorkingWk = dateadd(wk,-1,@WorkingWk)
end
end

Insert into #output (UntNum, Description, Sls_Year, Period, Avg_Wk_Sales)
select t.UntNum,t.txtUntNmDesc,convert(char,@EndYear),@period,
--(Select s.PrSls / @WksPr from DetailSlsMonthly s Where s.UntNum = @UntNum and s.SlsDate = @PrEnd)
(Select avg(wksls) from DetailSlsWeekly s Where s.UntNum = @UntNum and s.SlsDate >= @PrStart and s.SlsDate <=@WeekToUse and datepart(dw,s.SlsDate)=4)
From tblUnits t
Where t.UntNum = @UntNum
Select @period = @period + 1
end

select @EndYear = @EndYear - 1
select @numYears = @numYears - 1
end
end
Select * from #output where Avg_Wk_sales is not null
end
END
GO



It may be hopeless and I'll need to rewrite but any ideas are appreciated!

tj

 

[dmhirsch]

Possibly your @counter variable is initialized to zero, which would give (from your example) 8 iterations of the while loop: 0, 1, 2, 3, 4, 5, 6, 7. Two solutions:
1) Use < rather than <=
2) Initialize @count to 1 instead of 0