Alternative to this cursor?? - 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)

Alternative to this cursor??

Author

Topic

SqlShaun
Starting Member

14 Posts

Posted - 2005-05-23 : 09:17:30

I have an outlet table which contains info about outlets.
I have an address table which contains addresses for the outlets.

outlet table: (outlet)
iID, cName

Outlet Address Linking Table: (businessobjectaddress)
iObject, iAddress

address table: (addresslines)
iID, cLine1, cLine2

My problem is that i need the address for an outlet, but there are some outlets with multiple addresses, as i am joining across mulitple tables it is duplicating my records. I have solved this problem by using a cursor and inserting the first address found for that outlet into a temporary table and then joining on that table instead of directly joining on the address tables...........

create table #Addresses
(
iOutID int,
iAddressID int
)

declare addresscur cursor
for
select distinct [Outlet ID]
from #Data

declare @iOutAddy int

open addresscur

fetch next from addresscur into @iOutAddy

while @@fetch_status=0
begin
insert into #Addresses(iOutID, iAddressID)
select top 1 boa.iobject, boa.iaddress
from businessobjectaddress boa
where boa.iobject = @iOutAddy

update #Data
set [Address 1] =(select al.cline1
from addresslines al
inner join #Addresses ass on al.iid = ass.iAddressID
where ass.ioutid = @iOutAddy)
where [Outlet ID] = @iOutAddy

update #Data
set [Address 2] =(select al.cline2
from addresslines al
inner join #Addresses ass on al.iid = ass.iAddressID
where ass.ioutid = @iOutAddy)
where [Outlet ID] = @iOutAddy

fetch next from addresscur into @iOutAddy
end

close addresscur
deallocate addresscur

This works fine, but i want a way to do this without using a cursor.
Also... my #Data table contains multiple outlets, so i am using a cursor so i can work with one outlet at a time.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-05-23 : 09:23:02

Did you try making the join using the min or max functions to select the address record that would match the outlet record

I haven't try it yet but I think it should work and this way you can forget about the temp table and the loop

A candle loses nothing by lighting another candle

SqlShaun
Starting Member

14 Posts

Posted - 2005-05-23 : 10:32:20

I cant do that, as in my select statement, where i join on to the address tables, i select columns of type image, text and ntext. It does not allow me to group on columns of that data type. I guess i could do the max before i join on all my tables and then update that table once the address is in, but a cursor seems easier, even if i lose on performance. :-(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources