set rowcount and cursor 1

[Signit]

I am attempting to limit the number of rows returned while using a cursor in a stored procedure. The number of records to be selected is dynamic so select top X will not work. I am presently using set rowcount @selection_size but to no avail. Once I've retrieved my set of data I am storing it in a table. Any help would be appreciated. Code below:

    begin
      -- used to store random listing of machine_sks
      create table #random_machine_sk_list (random numeric(18,18),
                                            machine_sk int)
      
      -- create cursor for machines that meet criteria
      declare non_logged_machines_cursor cursor for
       select a.machine_sk
         from machine a
        where a.location_bldg_num = @location_bldg_num
          and a.machine_status = 'a'
          and a.os in ('windows 2000','windows xp')
      
      -- open up cursor and begin fetch
       open non_logged_machines_cursor
      fetch non_logged_machines_cursor
       into @r_machine_sk
      
      -- store random value for each machine_sk
      while (@@fetch_status <> -1)
        begin
          insert #random_machine_sk_list
                 (random, machine_sk)
          values (rand(), @r_machine_sk)
          fetch next from non_logged_machines_cursor into @r_machine_sk
        end
      
      -- destroy random generated cursor
      close non_logged_machines_cursor
      deallocate non_logged_machines_cursor
      
      -- create new cursor to store random generated data in log table
      -- get enough records to bring total to selection_size
      set rowcount @records_left
      declare records_to_insert cursor for
      select a.random,
             b.machine_sk,
             b.owner_cust_id,
             b.location_bldg_num
        from #random_machine_sk_list a,
             machine b
       where a.machine_sk = b.machine_sk
       order by random
      set rowcount 0
             
      -- open up cursor and begin fetch
        open records_to_insert
       fetch records_to_insert
        into @r_random,
             @f_machine_sk,
             @f_owner_cust_id,
             @f_location_bldg_num
      
      while (@@fetch_status <> -1)
        begin
          insert wfa_log
                 (machine_sk,
                 owner_cust_id,
                 location_bldg_num,
                 rcd_chg_date_time,
                 rcd_chg_agent)
          values (@f_machine_sk,
                 @f_owner_cust_id,
                 @f_location_bldg_num,
                 getdate(),
                 'STEELE,ANDREW H.')
          
           fetch records_to_insert
            into @r_random,
                 @f_machine_sk,
                 @f_owner_cust_id,
                 @f_location_bldg_num
        end

      drop table #random_machine_sk_list
      close records_to_insert
      deallocate records_to_insert
      return -200

 

[JohnDTampaBay]

Why not eliminate the SET ROWCOUNT all together.

Just before the line
while (@@fetch_status <> -1)
add
DECLARE @cntr int
SET @cntr = 0

Change the line
while (@@fetch_status <> -1)
to
WHILE (@@fetch_status <> -1) AND @cntr <= @records_left

Before the END for the cursor add the line
SET @cntr = @cntr + 1




--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Signit]

That totally works.


I found this note in the set rowcount section of SQL Server help:

Note Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers and data modification statements such as INSERT, UPDATE, and DELETE. The ROWCOUNT option has no effect on dynamic cursors, but it limits the rowset of keyset and insensitive cursors. This option should be used with caution and primarily with the SELECT statement

While John's suggestion is excellent, I am curious if there is a way to do this with a built in function in SQL. Any other thoughts?

 

[JohnDTampaBay]

You could change the initial
-- create cursor for machines that meet criteria
code to something like so:
-- create cursor for machines that meet criteria
set rowcount @records_left
select a.machine_sk
from machine a
into #rows
where a.location_bldg_num = @location_bldg_num
and a.machine_status = 'a'
and a.os in ('windows 2000','windows xp')
set rowcount 0

declare non_logged_machines_cursor cursor for
select machine_sk
from #rows

Then the remaining cursors will not need to be limited. They will only process the initial 50 rows pulled.


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Signit]

Thank you for thinking outside of the box for me. Another excellent suggestion John.