Loop within a loop?[REVISED] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Loop within a loop?[REVISED]

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-02-24 : 12:43:51

I need to have my head examined...
here is an update proc that fills a scheduling table. I am trying to convert this to update the table when a new user(s) are added.
this proc loops through the date ok, but how do I get it to advance the @user variable to the next user when there is more than one to add? Or am I going about this all wrong? (like that ever happens

CREATE procedure pr_UpdateTimeTable

as
set nocount on
  declare @StartDate datetime
  declare @EndDate datetime
  declare @SvcDate datetime
  declare @DateCount datetime
declare @user T_USR_ID

 

--set start date to today
set @StartDate = convert(datetime, convert(varchar(10), getdate(), 120))

--set end date to end of timetable 
select @EndDate = max(SVC_DAT) from TimeTable
select @SvcDate=SVC_DAT from TimeTable where SVC_DAT=@StartDate

set @DateCount = @StartDate
set @user=(select  top 1
s.nam
from sy_usr s
where is_servicer='y'
and s.nam not in(select nam from Timetable))
--loop through the dates and add our new servicers--
while @DateCount <= @EndDate

begin
insert TimeTable
(
SVC_DAT, NAM,
Time01, Time02, Time03, Time04,Time05, Time06, Time07, Time08,
Time09, Time10, Time11, Time12,Time13, Time14, Time15, Time16,
Time17, City01, City02, City03,City04, City05, City06, City07,
City08, City09, City10, City11,City12, City13, City14, City15,
City16, City17, Cust01, Cust02,Cust03, Cust04, Cust05, Cust06,
Cust07, Cust08, Cust09, Cust10,Cust11, Cust12, Cust13, Cust14,
Cust15, Cust16, Cust17
)
select @DateCount,
SY_USR.NAM,
dbo.udf_DefaultTime(@datecount, NAM, 1),dbo.udf_DefaultTime(@datecount, NAM, 2),dbo.udf_DefaultTime(@datecount, NAM, 3),
dbo.udf_DefaultTime(@datecount, NAM, 4),dbo.udf_DefaultTime(@datecount, NAM, 5),dbo.udf_DefaultTime(@datecount, NAM, 6),
dbo.udf_DefaultTime(@datecount, NAM, 7),dbo.udf_DefaultTime(@datecount, NAM, 8),dbo.udf_DefaultTime(@datecount, NAM, 9),
dbo.udf_DefaultTime(@datecount, NAM, 10),dbo.udf_DefaultTime(@datecount, NAM, 11),dbo.udf_DefaultTime(@datecount, NAM, 12),
dbo.udf_DefaultTime(@datecount, NAM, 13),dbo.udf_DefaultTime(@datecount, NAM, 14),dbo.udf_DefaultTime(@datecount, NAM, 15),
dbo.udf_DefaultTime(@datecount, NAM, 16),dbo.udf_DefaultTime(@datecount, NAM, 17),dbo.udf_DefaultTime(@datecount, NAM, 18),
dbo.udf_DefaultTime(@datecount, NAM, 19),dbo.udf_DefaultTime(@datecount, NAM, 20),dbo.udf_DefaultTime(@datecount, NAM, 21),
dbo.udf_DefaultTime(@datecount, NAM, 22),dbo.udf_DefaultTime(@datecount, NAM, 23),dbo.udf_DefaultTime(@datecount, NAM, 24),
dbo.udf_DefaultTime(@datecount, NAM, 25),dbo.udf_DefaultTime(@datecount, NAM, 26),dbo.udf_DefaultTime(@datecount, NAM, 27),
dbo.udf_DefaultTime(@datecount, NAM, 28),dbo.udf_DefaultTime(@datecount, NAM, 29),dbo.udf_DefaultTime(@datecount, NAM, 30),
dbo.udf_DefaultTime(@datecount, NAM, 31),dbo.udf_DefaultTime(@datecount, NAM, 32),dbo.udf_DefaultTime(@datecount, NAM, 33),
dbo.udf_DefaultTime(@datecount, NAM, 34),dbo.udf_DefaultTime(@datecount, NAM, 35),dbo.udf_DefaultTime(@datecount, NAM, 36),
dbo.udf_DefaultTime(@datecount, NAM, 37),dbo.udf_DefaultTime(@datecount, NAM, 38),dbo.udf_DefaultTime(@datecount, NAM, 39),
dbo.udf_DefaultTime(@datecount, NAM, 40),dbo.udf_DefaultTime(@datecount, NAM, 41),dbo.udf_DefaultTime(@datecount, NAM, 42),
dbo.udf_DefaultTime(@datecount, NAM, 43),dbo.udf_DefaultTime(@datecount, NAM, 44),dbo.udf_DefaultTime(@datecount, NAM, 45),
dbo.udf_DefaultTime(@datecount, NAM, 46),dbo.udf_DefaultTime(@datecount, NAM, 47),dbo.udf_DefaultTime(@datecount, NAM, 48),
dbo.udf_DefaultTime(@datecount, NAM, 49),dbo.udf_DefaultTime(@datecount, NAM, 50),dbo.udf_DefaultTime(@datecount, NAM, 51)

from SY_USR
where IS_SERVICER='Y' and nam=@user
set @DateCount = dateadd(dd, 1, @DateCount)
end
GO

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-02-24 : 16:01:09

Got it.
switched it around to use a cursor, now works fine:


CREATE  procedure pr_UpdateTimeTable

as
set nocount on
  declare @SQL T_SQL_QUERY
  declare @EndDate datetime
  declare @DateCount datetime
declare @user T_NAM

 
set @SQL='select s.nam
from sy_usr s
where is_servicer=''Y'' and s.nam not in(select nam from Timetable)'
begin
exec ('declare FC_CURSOR cursor for ' + @SQL)
    open FC_CURSOR
    fetch next from FC_CURSOR 
      into @user
while @@fetch_status=0
begin
--set start date to today
set @DateCount = convert(datetime, convert(varchar(10), getdate(), 120))

--set end date to end of timetable 
select @EndDate = max(SVC_DAT) from TimeTable


--loop through the dates and add our new servicers--
while @DateCount <= @EndDate

begin
insert TimeTable
(
SVC_DAT, NAM,
Time01, Time02, Time03, Time04,Time05, Time06, Time07, Time08,
Time09, Time10, Time11, Time12,Time13, Time14, Time15, Time16,
Time17, City01, City02, City03,City04, City05, City06, City07,
City08, City09, City10, City11,City12, City13, City14, City15,
City16, City17, Cust01, Cust02,Cust03, Cust04, Cust05, Cust06,
Cust07, Cust08, Cust09, Cust10,Cust11, Cust12, Cust13, Cust14,
Cust15, Cust16, Cust17
)
select @DateCount,
SY_USR.NAM,
dbo.udf_DefaultTime(@datecount, NAM, 1),dbo.udf_DefaultTime(@datecount, NAM, 2),dbo.udf_DefaultTime(@datecount, NAM, 3),
dbo.udf_DefaultTime(@datecount, NAM, 4),dbo.udf_DefaultTime(@datecount, NAM, 5),dbo.udf_DefaultTime(@datecount, NAM, 6),
dbo.udf_DefaultTime(@datecount, NAM, 7),dbo.udf_DefaultTime(@datecount, NAM, 8),dbo.udf_DefaultTime(@datecount, NAM, 9),
dbo.udf_DefaultTime(@datecount, NAM, 10),dbo.udf_DefaultTime(@datecount, NAM, 11),dbo.udf_DefaultTime(@datecount, NAM, 12),
dbo.udf_DefaultTime(@datecount, NAM, 13),dbo.udf_DefaultTime(@datecount, NAM, 14),dbo.udf_DefaultTime(@datecount, NAM, 15),
dbo.udf_DefaultTime(@datecount, NAM, 16),dbo.udf_DefaultTime(@datecount, NAM, 17),dbo.udf_DefaultTime(@datecount, NAM, 18),
dbo.udf_DefaultTime(@datecount, NAM, 19),dbo.udf_DefaultTime(@datecount, NAM, 20),dbo.udf_DefaultTime(@datecount, NAM, 21),
dbo.udf_DefaultTime(@datecount, NAM, 22),dbo.udf_DefaultTime(@datecount, NAM, 23),dbo.udf_DefaultTime(@datecount, NAM, 24),
dbo.udf_DefaultTime(@datecount, NAM, 25),dbo.udf_DefaultTime(@datecount, NAM, 26),dbo.udf_DefaultTime(@datecount, NAM, 27),
dbo.udf_DefaultTime(@datecount, NAM, 28),dbo.udf_DefaultTime(@datecount, NAM, 29),dbo.udf_DefaultTime(@datecount, NAM, 30),
dbo.udf_DefaultTime(@datecount, NAM, 31),dbo.udf_DefaultTime(@datecount, NAM, 32),dbo.udf_DefaultTime(@datecount, NAM, 33),
dbo.udf_DefaultTime(@datecount, NAM, 34),dbo.udf_DefaultTime(@datecount, NAM, 35),dbo.udf_DefaultTime(@datecount, NAM, 36),
dbo.udf_DefaultTime(@datecount, NAM, 37),dbo.udf_DefaultTime(@datecount, NAM, 38),dbo.udf_DefaultTime(@datecount, NAM, 39),
dbo.udf_DefaultTime(@datecount, NAM, 40),dbo.udf_DefaultTime(@datecount, NAM, 41),dbo.udf_DefaultTime(@datecount, NAM, 42),
dbo.udf_DefaultTime(@datecount, NAM, 43),dbo.udf_DefaultTime(@datecount, NAM, 44),dbo.udf_DefaultTime(@datecount, NAM, 45),
dbo.udf_DefaultTime(@datecount, NAM, 46),dbo.udf_DefaultTime(@datecount, NAM, 47),dbo.udf_DefaultTime(@datecount, NAM, 48),
dbo.udf_DefaultTime(@datecount, NAM, 49),dbo.udf_DefaultTime(@datecount, NAM, 50),dbo.udf_DefaultTime(@datecount, NAM, 51)

from SY_USR
where nam=@user
set @DateCount = dateadd(dd, 1, @DateCount)
end
 fetch next from FC_CURSOR 
        into @user
    end
    close FC_CURSOR
    deallocate FC_CURSOR
  end
GO

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources