Selecting records in a stored proc

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)

Selecting records in a stored proc

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-08 : 12:59:57

I have a Stored Proc which basically formats data in a table. The table has over 700k lines and it takes under an hour to run. The original syntax involved 9 seperate updates ran over the entire table each based on If statements (if coloumna = 2 update coloumna = 'Number2' and then on two the next) pretty much preforming 1 update to each of the 700k lines 9 times. I've rewritten so it's a dynamic query which goes though all these if statements builds @update and then executes it. Hopefully this describes what I'm looking at well enough.

I've created a while loop to go to the first line, create the @update syntax and then execute and then goto the next line based on the identity key.
(Don worry bout syntax.. thats not the problem)
declare @count int, @max int
set @count = min(id)
set @max = max(id)
while @count <= @max
begin
if statement making sure record with ID = @count does exist
multiple statements defining @update
exec (@update)
@count = @count+1
end

Theres mostly what it looks like. Is there a way to do something simular (maybe using a for loop) without depending on the ID coloumn?
Cursors kinda have a movenext command, but cursors can't be used as they are just way to big... same with a temp table. any ideas? or would it be best to insist that this table needs the ID coloumn to work.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-08 : 13:07:15

quote:
(Don worry bout syntax.. thats not the problem)

Unfortunately it's a problem for us, because you've left out some critical code that tells us exactly what you're doing and how you're doing it. Can you post the entire code you have now? It'll be a big help.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-08 : 13:13:40

well sure

Create Proc Res_Interp_update
as
declare @count Int, @max Int, @interpretation_depth varchar(20), @update varchar(5000)
select @count = min(pkid) from resource_interpretation_event_dts
select @max = max(pkid) from resource_interpretation_event_dts

while @max >= @count
Begin
--set all update to default
set @update = 'update resource_interpretation_event_dts set '
--begin set of if statements to create update statement

If (select interpretation_date_time from resource_interpretation_event_dts where pkid = @count) = '00000000' set @update = @update +'interpretation_date_time = null'
Else set @update = @update + 'new_interpretation_date_time = convert (datetime, (select interpretation_date_time from resource_interpretation_event_dts where pkid='+convert(varchar(10),@count)+'))'

--There is a few more if and set @update = @update here.... well.. okay, try about 20 more...

Select @update = @update + ' where pkid = '''+convert(varchar(10),@count)+''''
Exec (@update)
-- Increse counter by one
set @count = @count + 1
end

hopfeully it pasted still readable. pkid is the id coloumn they assigned to it.. identity (1,1). This table is purged nightly and new data is loaded into it to be formatted and viewed.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-08 : 13:17:25

quote:
--There is a few more if and set @update = @update here.... well.. okay, try about 20 more...

quote:
Can you post the entire code you have now?

Don't want to be a pain, but I really need to see EVERYTHING.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-08 : 13:27:24

I had to edit out pieces... They keep security here kinda stingy.. anywhere it says a or b just assume a table or coloumn name. I wish I could give more but this is all I can do. The formata procedure inserts and formats data into a different table and returns to this. If this isn't enough, sorry.. can't do then... but im more looking for more a way to scroll through rows in a table without using cursors or an identity coloumn.

Create Proc Res_Interp_update
as
declare @count Int, @max Int, @avarchar(20), @update varchar(5000)
select @count = min(pkid) from a
select @max = max(pkid) from a

while @max >= @count
Begin
--set all update to default
set @update = 'update a set '
--begin set of if statements to create update statement

If (select a from a where pkid = @count) = '00000000' set @update = @update +'a = null'
Else set @update = @update + 'a = convert (datetime, (select b from b where pkid='+convert(varchar(10),@count)+'))'

If (select a from b where pkid = @count) = '00000000' set @update = @update + ',b = null'
Else set @update = @update +',a = convert (datetime, (select b from b where pkid='+convert(varchar(10),@count)+'))'

If (select a from a where pkid = @count ) in ('b' , 'a')
Begin
-- run proc formata
exec formata
set @update = @update + ',a = ''a'',a = ''a'''
End
If (select interpretation_type from resource_interpretation_event_dts where pkid = @count) = 'a'
Begin
select @interpretation_depth = interpretation_depth from resource_interpretation_event_dts where pkid = @count
if @interpretation_depth = '1' set @update = @update + ',a = ''b'''
if @interpretation_depth = '2' set @update = @update + ',a = ''b'''
if @interpretation_depth = '3' set @update = @update + ',a = ''b'''
End
Select @update = @update + ' where pkid = '''+convert(varchar(10),@count)+''''
Exec (@update)
-- Increse counter by one
set @count = @count + 1
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources