Need query help - row processing 1

[icemel]

Hi,

This is my question.

I have 100 or so rows. I want to do some processing on these rows, 10 at a time.

The rows are identified by id numbers, that are in numerical order.

So, I want to process rows 1 - 10, pause. Process rows 11 - 20, pause. Etc.

Does anyone have a simple method for doing this? And does t-sql allow you to "pause" between statements?

Although I'm not really sure what the benefit of pausing is, someone here requested it be done this way... but if the pause is only for a few seconds or whatever, truly, what difference does this make? Why not just do all 100 rows at a time? Any comments?

Thanks much

 

[SQLDenis]

to pause 10 seconds use this
WAITFOR DELAY '00:00:10'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

here is some looping code

declare @MaxID int,@loopid int
select @loopid = 1,@MaxID = 100
while @loopid < @MaxID
begin

select * from table1 where id between @loopid and @loopid + 10
--do your stuff here
set @loopid = @loopid + 10
WAITFOR DELAY '00:00:10'
end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Nagrom]

I'm not sure why you would want the pause either, but you could loop through fetching 10 records at a time based on the ID. Assuming you have an integer variable "intStartingRow", a database connection "objConn", a recordset object "objRS", and a string variable "strSQL" you could do it like this:


intStartingRow = 1

strSQL = "select * from MyTable where [ID] >= " & CStr(intStartingRow) & " and [ID] < " & CStr(intStartingRow + 10)
Set objRS = objConn.Execute(strSQL)

Do While Not objRS.EOF
Do While Not objRS.EOF
' Process your row
objRS.MoveNext
Loop
objRS.Close
intStartingRow = intStartingRow + 10
strSQL = "select * from MyTable where [ID] >= " & CStr(intStartingRow) & " and [ID] < " & CStr(intStartingRow + 10)
Set objRS = objConn.Execute(strSQL)
Loop
objRS.Close

 

[gmmastros]

>> I want to do some processing on these rows, 10 at a time.

There was something similar just a couple days ago, thread183-1211057.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Nagrom]

Whoops. Sorry about that - I thought I was in the ASP forum.

 

[icemel]

yes, thanks for referring me to my other post - i didn't really look at it in depth

sorry, my sql is so rusty........ i used to be quite good, about 5 years ago when i did a lot of development for both oracle and sql server...... now i don't do it much, so i get lazy !