I use SQL 2000.
Is there any restriction in using nested cursors(3 levels) on the same base table ?
There isn't a limitation, but why on earth would you do it? What are you trying to achieve? This would be a performance killer. Also, be aware your cursors will probably need to be static or else you'll end up stuck in the fetching loop.
Seriously though, cursors are bad. I rewrote our MRP routine (cursor based) to a VB 6 application and the 50 minute routine now takes approximately 8 minutes.
I have a front end ACCESS program that transfers a table to SQL server and then based on the table values it has to update multiple tables. The multiple tables' names are based off of a field value in this transferred table , hence the need for a cursor to locate the table names and then a nested cursor to actually update that table. Does that make sense ? Is there any better way to do this ?
Any help will be appreciated.
To provide real assistance I'll need a general idea of the table structure transferred from Access and I have a feeling the solution that will work in the end is a rewrite of the Access front end process, but I'll hold off on that.
Thanks for your offer to help.
My table structure is :
CREATE TABLE [tbl_group] ( [StaffID] [int] NULL , [TrackOrderID] [int] NULL , [Qualifier] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LSeries] [int] NOT NULL , [LSeriesunit] [int] NOT NULL , [Image_Filename] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Display_Image] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Page] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Sort_Order] [int] NULL , [URL_Path] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [File_Size_Bytes] [int] NULL , [Daily_RHD_No] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Daily_Date_Transferred] [smalldatetime] NULL , [Archival_RHD_No] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Archival_Date_Transferred] [smalldatetime] NULL , [Storage_Array_Transfer] [smalldatetime] NULL , CONSTRAINT [PK_tbl_group] PRIMARY KEY CLUSTERED ( [Qualifier], [LSeries], [LSeriesunit], [Image_Filename]  WITH FILLFACTOR = 90 ON [PRIMARY]
The ACCESS program does the following:
1. The data in this table is tranferred to SQL server .
2. a stored procedure from SQL server is called that does the following:
..continued ( sorry pressed the wrong key )
A stored procedure from SQL server is called that does the following:
a. creates a cursor that determines the distinct values for field LSeries in tbl_group
b. if we call the distinct value as YYY, the procedure looks up table tblfilename_YYY and update all the matching rows from tbl_group to this table.
The problem arises when there is more than one distinct value of Lseries, which means when more than one table has to be update, the procedure times out even of there are 10,000 rows. But if the procedure has only one discitnct value of Lseries it can go on for even 150,000 records.
Hence I am trying to re-write to code diferently !!
Thanks again for your help.
Please refer to point 'a' of my last reply.
The table can have more than one distinct value of Lseries, and that is allowed. For eg, in a group of 10,000 records in the table there could be 4 distinct values of Lseries.
I guess, I defintely will have to re-design .
I presume that LSeries can be any value or is it as per your example limited to 999?
But why a nested cursor!
One cursor to get distinct LSeries and do a set based update to update target table. Unfortunately this would involve using dynamic sql ( yes I know to be avoided at all costs but... )
if the LSeries is limited to 999 say then create 999 procs to do each table update and then one cursor to get distinct LSeries and EXEC the relevant proc
to do similar to above with cursor you could put distinct LSeries into temp table, select top one, do update, delete the one selected and loop back round until no rows left
Far away is close at hand in the images of elsewhere.