While Statement in SQL

Dear Experts,
I am using below mentioned statement; the system date is 17th Jan-13. The loop should be run only two times because I set @DT date 16th Jan-13 but it is running continuously till I stop it physically.
Please help what I am doing wrong.
Rgds.
Mehram


Set @CC='01'
Set @CYear='1213'
Set @Branch='KHI'
--Set @Sd='1/1/2013'
Set @Dt='01/16/2013'
Select @Holiday=dt from Holidays Where dt=@Dt

While @Dt < Convert(DateTime, Convert(Varchar(12), GetDate()),112)
Select Transno=CC+EmpCode+cast(datepart(yy,@dt)as varchar) + right('00'+cast(datepart(mm,@dt)as varchar),2)+ right('00'+cast(datepart(dd,@dt)as varchar),2), TransNoEmpInfo=TransNo
            , Months=((CONVERT([varchar](10),left(datename(month,@Dt),(3)),(0))+'-')+CONVERT([varchar](10),datepart(year,@Dt),(0)))
            ,WeekDay=(datename(weekday,@Dt))
            ,AttDt=@Dt, CC, cATEGORY, EmpCode,EmpName, FatherName, NICNo, Designation, Department,
            Status=Case When (DateName(dw,@Dt)='Sunday' or DateName(dw,@Dt)='Saturday') then 'O' else
                     Case When @Holiday=@Dt Then 'O' else 'P' end end
            ,FA1=Case When FA=1 then 1 else 0 end
            ,HA1=Case When HA=1 and (@Holiday is not null or (datename(w,@dt)='Sunday' or DateName(dw,@Dt)='Saturday')) then 1 else 0 end
            ,Holiday=Case When (@Holiday is not null or (DateName(dw,@Dt)='Sunday' or DateName(dw,@Dt)='Saturday')) Then 'Y' else 'N' end
            ,Cyear=@Cyear --, RegsinedOn
from emp_info a
Where CC=@CC and @Dt Between coalesce(JoiningDate,@Dt) and coalesce(RegsinedOn,@Dt) and Branch=@Branch
ORDER BY Department,cATEGORY, empCode  
set @Dt = @Dt+1
MehramAsked:
Who is Participating?
 
thatmsftbuguyConnect With a Mentor Commented:
I would add a Begin and End Statement to your code:

Set @CC='01'
Set @CYear='1213'
Set @Branch='KHI'
--Set @Sd='1/1/2013'
Set @Dt='01/16/2013'
Select @Holiday=dt from Holidays Where dt=@Dt

While @Dt < Convert(DateTime, Convert(Varchar(12), GetDate()),112)
BEGIN
Select Transno=CC+EmpCode+cast(datepart(yy,@dt)as varchar) + right('00'+cast(datepart(mm,@dt)as varchar),2)+ right('00'+cast(datepart(dd,@dt)as varchar),2), TransNoEmpInfo=TransNo
            , Months=((CONVERT([varchar](10),left(datename(month,@Dt),(3)),(0))+'-')+CONVERT([varchar](10),datepart(year,@Dt),(0)))
            ,WeekDay=(datename(weekday,@Dt))
            ,AttDt=@Dt, CC, cATEGORY, EmpCode,EmpName, FatherName, NICNo, Designation, Department,
            Status=Case When (DateName(dw,@Dt)='Sunday' or DateName(dw,@Dt)='Saturday') then 'O' else
                     Case When @Holiday=@Dt Then 'O' else 'P' end end
            ,FA1=Case When FA=1 then 1 else 0 end
            ,HA1=Case When HA=1 and (@Holiday is not null or (datename(w,@dt)='Sunday' or DateName(dw,@Dt)='Saturday')) then 1 else 0 end
            ,Holiday=Case When (@Holiday is not null or (DateName(dw,@Dt)='Sunday' or DateName(dw,@Dt)='Saturday')) Then 'Y' else 'N' end
            ,Cyear=@Cyear --, RegsinedOn
from emp_info a
Where CC=@CC and @Dt Between coalesce(JoiningDate,@Dt) and coalesce(RegsinedOn,@Dt) and Branch=@Branch
ORDER BY Department,cATEGORY, empCode  
set @Dt = @Dt+1

END
0
 
MehramAuthor Commented:
Great, Can you share what would do Begin and End in this case
0
 
thatmsftbuguyCommented:
Begin and END are always used in a control a flow statement like While Loops or Do Until Loops
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.