Solved

While Statement in SQL

Posted on 2013-01-16
3
327 Views
Last Modified: 2013-01-16
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
0
Comment
Question by:Mehram
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
thatmsftbuguy earned 500 total points
ID: 38785904
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
 

Author Comment

by:Mehram
ID: 38785913
Great, Can you share what would do Begin and End in this case
0
 
LVL 2

Expert Comment

by:thatmsftbuguy
ID: 38785932
Begin and END are always used in a control a flow statement like While Loops or Do Until Loops
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql + top 1 for each customer 3 66
display data in text field from data base for updating 6 71
Unable to save view in SSMS 21 81
Addition to SQL for dynamic fields 6 56
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question