I hate cursors too, but.... - 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)

I hate cursors too, but....

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-09 : 22:27:58

I can't see a better way to do this. The following proc selects all users from SY_USR that are servicers who are not already in dbo.timetable, and then inserts the user (one row for each day per servicer(don't even get me going on the denormalized

part of this; I know, Iknow!! I inherited this; talk about not being able to choose your family!

)) into the table. The only way I can see to loop through all users is with a cursor; anyone see something better?
(loops within loops; what's the world coming too?)


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...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-10 : 14:31:41

If you had a Calendar table (Permanent or Ad Hoc), would this get you closer?

set @DateCount = convert(datetime, convert(varchar(10), getdate(), 120))
select @EndDate = max(SVC_DAT) from TimeTable

insert into TimeTable (
SVC_DAT, NAM,
Time01, Time02, Time03, Time04,
...
)
select
c.Today, s.nam,
dbo.udf_DefaultTime(@datecount, NAM, 1),
dbo.udf_DefaultTime(@datecount, NAM, 2),
...
from sy_usr s
cross join dbo.Calendar c
where is_servicer=''Y''
and s.nam not in(select nam from Timetable)
and c.Today between @DateCount and @EndDate

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-10 : 15:52:07

Hey Bustaz,
That's a possibility. There is a calendar table in this DB, so we could start there and build each entry from the @datecount. I know set based is the way to go, but I just wonder if a cursor in an instance like this is really such a bad thing(here comes the drubbing for me!). Anyway, I'll take your idea and play with it a bit, and see what kind of difference in performace it makes.
I'll keep everyone posted!
Andy

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-10 : 19:21:34

Cursors are typically 5-10 times slower than a set based solution. There are exceptions, of course, so you'll only really know by testing. Having said that, this one feels like a set based solution waiting to happen. If it turns out to be slower, check the indexes and query execution plan before reverting to the cursor approach.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-04-10 : 22:10:10

Yeah, I think you're right about that. This procedure doesn't run all that often, but the more cursors I get out of this DB, the better. I've come across some pretty wild stuff here, some that we cannot change due to restrictions with the app interface. The app passes filter text to procedures in such a way that it is somewhat difficult to convert EVERYTHING to set-based; some of these cursors will just have to stay. I'll let you know what happens with this one!
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