May 21, 2004 at 1:50 pm
Can you declare a cursor fast-forward within a cursor?
I need to get a group of records. Then scan this group, getting all matches from parent table and sorting by sales figure. Then within each of the groups I want to update the secondary cursor with a ranking to get the rank each item has within the group. Then continue scanning the first cursor on the next group repeating the steps in cursor two.
May 24, 2004 at 4:06 am
There are probably lots of 'clever' things that you can do with cursors, but why would you bother?
Learn how to code SQL properly (ie. without cursors) or switch to working with Oracle.
If you need help on converting your cursors to something more elegant then post more details and I'll be happy to oblige.
May 24, 2004 at 4:15 am
I would love to see a better way! This project requires determining 8 different rankings for the Wal-Mart stores. In this sample, I worked out the updates without nesting cursors. But I am still using a cursor. I do not know of a way to increment the records (ranking) in another way in T-SQL for Sequel Server. In this project using Oracle is not an option.
The cursor.
/* First declare the cursor, which places it in memory for processing
used the keyword "fast_forward" to determine the type of cursor
this is a server based cursor which processes each record from the first to the last
I selected three fields to work with.
Store Nbr which is my key index field to link Report1 to Report 1 tables
TYTDSales to produce my order for ranking
And StoreName for order and rank grouping
*/
declare cgm cursor fast_forward for
SELECT [Store Nbr],StoreName,TYTDSales FROM Report1
order by StoreName, TYTDSales Desc
-- We next declare some temporary holding values which are called variables.
-- Each are preceeded by the @ character, which makes these local variables
-- a local variable is automatically released when this routine is finished
DECLARE
-- The @StoreNbr variable is the current value of the field [Store Nbr]. We name these variable the same as the field name
@StoreNbr int,
-- The @StoreName variable is the current value of the field storename. We name these variable the same as the field name
@StoreName varchar(255),
-- The @TYTDSales variable is also needed. Every field selecting into the cursor needs to included in the variables declared
@TYTDSales Float,
-- Next we will declare custom local variable we need for processing temporary storage units.
-- @Rank variable stores the rank value, which we increment for each store in the cursor being processed
-- Place a comma after each variable declared to produce a list of varables under the one declaration statement
@rank int,
-- We will need a variable to track when the store type changes. we latter compare this to the current store type
@CurrentStoreType Varchar(255)
-- Store our first store type to our control variable @CurrentStoreType
-- Set @CurrentStoreType = @StoreName
-- Give the variables a value to initialize them
SET @rank=1
-- open the cursor to start processing it
open cgm
-- get the next record into our @storename variable.
-- Since it is the same name as the field it will "know" what field value to use
-- This select into cursor fetch must have the same list of variables as our original cursor select statment.
fetch next from cgm into @StoreNbr,@StoreName,@TYTDSales
-- Store our first store type to our control variable @CurrentStoreType
Set @CurrentStoreType = @StoreName
-- Continue processing until we are at the end of the cursor (@@fetch_status=0 did not find more records)
-- If we were at the end of file, processing would terminate here
while @@FETCH_STATUS = 0
-- Begin the real work
BEGIN
-- Replace our final result set into [Report 1] table
UPDATE [Report 1] SET [Store Type Rank by Sales]=@Rank
Where [Store Nbr] = @StoreNbr
-- Are we on the same storetype?
if @CurrentStoreType <> @StoreName
-- Since the next statement has more than one processing steps I added a Begin and End statement to enclose them
Begin
-- Increment our rank number variable @Rank as long as we are in the same store type
SET @rank=1
-- Store new store type to our control variable @CurrentStoreType
Set @CurrentStoreType=@StoreName
End
-- Start over with ranking number if we are on a new store type
Else
SET @rank=@rank+1
-- Get the next record and store to our @StoreNbr,@StoreName,TYTDSales variables
-- This select into cursor fetch must have the same list of variables as our original cursor select statment.
fetch next from cgm into @StoreNbr,@StoreName,@TYTDSales
END
CLOSE cgm
deallocate cgm
May 24, 2004 at 4:50 am
How's this?
It loops through each storetype, but within the loop, it just does a couple of set-oriented operations, so would be very fast, even if you have thousands of stores of each type (apologies for the layout - this site plays havoc with my indentation for some reason):
create proc doranks
as
declare @storetype varchar(50)
--retrieve the list of storetypes
select distinct
storename
into
#storetypes
from
report1
--loop for each storetype
while exists (select * from #storetypes) begin
create table #rankings (
ranknum int identity(1,1),
storenbr int
)
select
@storetype = storename
from
#storetypes
--order by stores by descending sales
insert
#rankings (storenbr)
select
storenbr
from
report1
where
storename = @storetype
order by
tytdsales desc
--set the value in the original table
update
a
set
a.storetyperankbysales = b.ranknum
from
report1 as a
join #rankings as b on b.storenbr = a.storenbr
--remove the storetype which has been processed
delete
#storetypes
where
storename = @storetype
--drop & recreate the table to reset the identity value
drop table #rankings
end
May 24, 2004 at 5:58 am
This works (with a few minor field name changes) issue was [Report 1] table is to be updated, This report table does not have the storename field. Report1 (a work table) to produce on ranking at a time is how I set it up now. Maybe it is not the best way. I am also trying to find a way to make this completely generic. Different companies have different requirments for this update. And the end users will need to make these changes.
Example.
1) ranking by sales by storetype
2) ranking by growth [% Change] by District.
possible using your procedure method I could pass parameters for the select statement needed for the selection, the field name to be updated, the field to rank
Why would this procedure which writes and drops a temp table, be better and faster than using the cursor method?
May 24, 2004 at 6:21 am
Sorry, I didn't notice the space in [report 1]. I'd really recommend that you don't use spaces in identifiers - they're more trouble than they're worth.
Anyway, I think the amendment below should do it.
As for making the procedure generic, I'd suggest that you create a different procedure for each type of ranking and then pass storetype as a parameter. This should lead to more clarity in each procedure - if you try to genericise too much then it might get quite messy, and there's nothing wrong with separating different functionality into separate procs.
As for performance, I'm certain that this will generally be much faster than using a cursor. Within the while loop, all of the operations are set-oriented. eg. if you have 1,000 stores to process, your current code will do 1,000 individual updates of the Report 1 table, whereas the new code will do 1 update operation on 1,000 rows. The overhead of creating a temp table is insignificant, and probably as fast as declaring and closing your cursor.
Regards
Rob
create proc doranks
as
declare @storetype varchar(50)
--retrieve the list of storetypes
select distinct
storename
into
#storetypes
from
report1
--loop for each storetype
while exists (select * from #storetypes) begin
create table #rankings (
ranknum int identity(1,1),
storenbr int
)
select
@storetype = storename
from
#storetypes
--order by stores by descending sales
insert
#rankings (storenbr)
select
storenbr
from
report1
where
storename = @storetype
order by
tytdsales desc
--set the value in the original table
update
a
set
a.storetyperankbysales = b.ranknum
from
[report 1] as a
join #rankings as b on b.storenbr = a.storenbr
--remove the storetype which has been processed
delete
#storetypes
where
storename = @storetype
--drop & recreate the table to reset the identity value
drop table #rankings
end
May 24, 2004 at 6:32 am
Thanks for a good answer and good directions to be following.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply