variable tablename in cursor select stmt
-
I am trying to write a stored procedure that has nested cursors.
The table name of the second level cursor changes with every fetch stmt of the first cursor. Hence it has a variable name for its table name in its SELECT stmt. But I keep getting an error
Here is the sample code
OPEN cursor1
FETCH cursor1 into @countyt
while @@FETCH_STATUS = 0
BEGIN
DECLARE cursor3 CURSOR FOR
SELECT County,Qualifier,Lseries from LandRecords..tblLandRec_Index_Series where County=@countyt
FOR READ ONLY
-- SELECT @countyt
OPEN cursor3
FETCH cursor3 into @county, @Qualifier, @series
while @@FETCH_STATUS = 0
BEGIN
SET @filename = 'tblFilenames_' + @Qualifier + @series
print @filename
set @filename = 'Volume_Control' + '.dbo.' + @filename
print @filename
DECLARE cursor2 CURSOR
GLOBAL
DYNAMIC
FOR
SELECT sum(file_size_bytes) , Lseries from @filename group by Lseries
FOR READ ONLY
I get an error :
Must declare the variable @filename for the last SELECT
Any help would be appreciated.
-Thri
-
Try creating a temp table right before you declare your last cursor. Have your cursor select from the temp table.
-
jxflagg is correct. If you create a temp table and then use dynamic sql to fill it you should be able to create a cursor against it with no problems. BUT, is there anyway you can solve this issue without nesting cursors 3 levels deep? This sounds like a really performance degrading routine to me.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Change the cursor2 declare to this
declare @sql nvarchar(4000)
set @sql = '
DECLARE cursor2 CURSOR
GLOBAL
DYNAMIC
FOR
SELECT sum(file_size_bytes) , Lseries from ' + @filename + ' group by Lseries
FOR READ ONLY
'
exec(@sql)
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for your suggestions. This is a wonderful group with everybody giving such prompt responses.
Thanks David, the exec enclosing the cursor works.
But I have been toying with the idea of creating a temp table as jxflagg and Gary have suggested. Would a temp table be better as far as performance goes ? Is there anyway I can these cursors into joins so that I can avoid the cursors ? I am a novice at creating joins.
Thanks again.
-
The problem is that your table names are in a table and that is why you are using cursors.
How many 'Volume_Control.tblFilenames_' tables are there and what size are they (rows). Is the number fixed or variable.
If you want all the results in one recordset then the use of a temp table would be required.
A join would be difficult as per above. The only way I can see to use a join would be to union all the tblFilenames_ together and join that result with LandRecords..tblLandRec_Index_Series.
Far away is close at hand in the images of elsewhere.
Anon. -
The number of tables are about 150 now and the number of rows anywhere from 0 to a million in each table. The number of tables and rows can increase in future as they get updated once a week/2 weeks. The table name has to be obtained dynamically since the it put together as tblFilenames_XY based on X and Y fields that I can get that from tblLandRec_Index_Series and another table taht has similar info. From each table tblFilenames_XY, I need only 1-5 rows of data required for calculating statistics that I would put into a temp table . These would be eventually displayed using ACCESS (hopefully !). Would these nested cursors degrade the performance severely ?
-
I think the real problem is that the schema violates Codd's Information Rule by having identifiers (i.e. table names) connote information. That's why you now need cursors, dynamic SQL, temporary tables, UNIONs and other kludges. Do not create multiple tables with exactly the same columns but differing names; instead create one table with an additional column to hold the information you added to the table names. SQL Server works very well with large tables.
--Jonathan
--Jonathan -
What would be your max number of tables per @countyt?
I'm thinking something in the line of
(David's modified)
declare @sql nvarchar(4000)
set @sql = 'DECLARE cursor2
CURSOR GLOBALDYNAMIC FOR
SELECT Convert(Int, 0) as SumOf_file_size_bytes , Convert(Char(x), NULL) as Lseries WHERE 1=2 -- Dummy to establish structure' + Char(13) + Char(10)
Select @sql = @sql +
'UNION ALL SELECT sum(file_size_bytes) , Lseries
from Volume_Control.dbo.tblFilenames_' + Qualifier + Lseries + '
group by Lseries FOR READ ONLY' + Char(13) + Char(10)
From LandRecords..tblLandRec_Index_Series where County=@countyt
exec(@sql)
Once you understand the BITs, all the pieces come together
-
Thomas, Per @countyt I could have 100 to 150 rows. I will try out the sql stmt that you have mentione and get back on how it worked.
Jonathan, This database was designed well before i got here and there is too much data now , so we cannot change the design but it is agreed here that there should nto have been 2 tables with the same stucture. So i have to just work with it.
-
Thanku very much friends. The sproc works well now.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply