CODEWITHSUNDEEP
sql-servert-sql
Orgra
Orgra

Reputation: 31

While loop to populate a table

I am trying to use a while loop to populate a table in SQL Server. The data is to be in 5 minute increments from 8:00 am to 7:00 pm for Monday through Friday.

Below is the code that I have in place when I try to pass the variable @Beg to @Temp I get an error:

Msg 102, Level 15, State 1, Line 85
Incorrect syntax near '@Temp'.

Msg 102, Level 15, State 1, Line 90
Incorrect syntax near '@Beg'.

My code:

Create Table TIMESLOT
(
    TIMESLOTID  int not null identity(1,1),
    Beg_Time    datetime not null,
    End_Time    datetime not null,
    TimeDayOFWeek   varChar(25) not null
);

DECLARE @Beg as Time;
DECLARE @Temp as Time;
DECLARE @End as Time;
DECLARE @Day as dayofweek;

set @Beg = '08:00 AM';
Set @End = '07:00 PM';
set @Day = 'Monday';

While (@Day != 'Saturday' )
Begin
    While (@Beg <= @End)
    Begin
        @Temp = @Beg;

        DateAdd(minute,5,@Temp)

        Insert into TIMESLOT (Beg_Time, End_Time, TimeDay0fWeek) 
        Values (@Beg, @Temp, @Day)

        if (@Beg <= @End)
            @Beg = @Temp
        Else
            @Beg = '08:00 AM'
            @Day = Datadd(day, 1, @Day)
        End
    End
End

Upvotes: 2

Views: 2729

Answers (2)

Orgra
Orgra

Reputation: 31

This is the code that finally fixed the issue, Wheels73 was correct my syntax was wrong. But I also had the if statement and the variable @beg reset in the wrong area.

While (@Day != 'Saturday' ) -- first end
Begin
    While (@Beg < @End)
    Begin--Second
        Set @Temp = @Beg
        Set @Temp = DateAdd(MINUTE,5,@Temp)
        Insert into TIMESLOT (Beg_Time, End_Time, TimeDayOFWeek) 
        Values (@Beg, @Temp, @Day)
        Set @Beg = @Temp
    End--Second
    Set @Beg = '08:00 AM'
    if(@Day = 'Monday')
        Begin
            Set @Day = 'Tuesday'
        End
    Else if(@Day = 'Tuesday')
        Begin
            Set @Day = 'Wednesday'
        End
    Else if (@Day = 'Wednesday')
        Begin
            Set @Day = 'Thursday'
        End
    Else if (@Day = 'Thursday')
        Begin
            Set @Day = 'Friday'
        End 
    Else 
        Begin
            Set @Day = 'Saturday'
        End
End--firstenter code here

Upvotes: 0

Wheels73
Wheels73

Reputation: 2890

try the below. Your syntax was not quite correct

Create Table TIMESLOT
(
    TIMESLOTID  int not null identity(1,1),
    Beg_Time    datetime not null,
    End_Time    datetime not null,
    TimeDayOFWeek   varChar(25) not null
);

DECLARE @Beg as Time;
DECLARE @Temp as Time;
DECLARE @End as Time;
DECLARE @Day as dayofweek;

set @Beg = '08:00 AM';
Set @End = '07:00 PM';
set @Day = 'Monday';

While (@Day != 'Saturday' )
Begin
    While (@Beg <= @End)
    Begin
        Set @Temp = @Beg;
        Set @Temp = DateAdd(MINUTE,5,@Temp)
        Insert into TIMESLOT (Beg_Time, End_Time, TimeDayOFWeek) 
        Values (@Beg, @Temp, @Day)
        If (@Beg <= @End)
        Begin
            Set @Beg = @Temp
        End
        Else
        Begin
            Set @Beg = '08:00 AM'
            Set @Day = DateAdd(day,1, @Day)
        End
    End
End

Upvotes: 1

Related Questions