Cursor Loop

Hi, I wonder can someone help me. I have a cursor that will drop a number of
wly tables if a monthly table is being generated. I have to keep the
previous ws data but can drop the remaining tables. I have tried it as
below but it will only drop the wk214 table as calculated and does not carry
on to drop wk213,wk212 etc.
Does anybody know how I can acheive this?
Begin
DECLARE lcurTables_To_Be_Deleted CURSOR FOR
SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
OPEN lcurTables_To_Be_Deleted
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
PRINT '1'
PRINT @.TableNumToDrop
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
PRINT '2' -- wk214
PRINT @.TableNumToDrop
while @.@.fetch_status = 0
begin
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
+ @.TableNumToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
Dropped.' PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TableToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTables_To_Be_Deleted
EndMake sure you declare the cursor insensitive. Also, did you initialize all
your variables?
Seriously though, why would you want to prolong the life of such a poor
design by developing more code like this? This is so abysmal it breaks just
about every rule in the book: cursors; dynamic SQL; use of system tables;
modifying metadata. Maybe you aren't concerned, but this sort of thing
shouldn't get past even the most basic audit or compliance review.
How about:
DELETE FROM SomeTable
WHERE the_date < @.some_date
Simpler, no?
Take care.
David Portas
SQL Server MVP
--|||I have had this debate before David.
My new model design is in the pipeline with management.
Until then I am stuck with this.
The issue I have is why when the part that calculates the w number is run
it goes into the loop. But it only goes in once. Have I missed something
blatent in the code that prevents it from going into the loop for the other
tables returned from the cursor definition
"David Portas" wrote:

> Make sure you declare the cursor insensitive. Also, did you initialize all
> your variables?
> Seriously though, why would you want to prolong the life of such a poor
> design by developing more code like this? This is so abysmal it breaks jus
t
> about every rule in the book: cursors; dynamic SQL; use of system tables;
> modifying metadata. Maybe you aren't concerned, but this sort of thing
> shouldn't get past even the most basic audit or compliance review.
> How about :
> DELETE FROM SomeTable
> WHERE the_date < @.some_date
> Simpler, no?
> Take care.
> --
> David Portas
> SQL Server MVP
> --
>|||> My new model design is in the pipeline with management.
Good news :-)
I haven't tested it out but I suspect the cursor terminates because dropping
the table deletes the current row. That's why I suggested you declare the
cursor INSENSITIVE. Alternatively, select the table names into a table
variable and iterate through that.
Again, note that you really should avoid system tables where you can. For
this I would prefer to use INFORMATION_SCHEMA.TABLES.
Hope this helps.
David Portas
SQL Server MVP
--
"marcmc" wrote:
> I have had this debate before David.
> My new model design is in the pipeline with management.
> Until then I am stuck with this.
> The issue I have is why when the part that calculates the w number is r
un
> it goes into the loop. But it only goes in once. Have I missed something
> blatent in the code that prevents it from going into the loop for the othe
r
> tables returned from the cursor definition
>
> "David Portas" wrote:
>|||I execute ...
exec uspDB_Maint_Archive_Snaps 'FAT_SNAP_PO_RISK_DETAIL_mth', 216, 'DbName'
Then I declare cursor...
Begin
declare lcurTables_To_Be_Deleted cursor for
select substring(NAME,28,3) from SNAPS_ARC..SYSOBJECTS(nolock) where name
like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
open lcurTables_To_Be_Deleted
fetch lcurTables_To_Be_Deleted into @.TabNoToDrop
This brings the following into the cursor
--
215
214
213
212
212
I want to keep 215 but delete the rest
I want the next few lines of to do this. But I cant seem to find a way.
Please help...
SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- 215
SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
SELECT @.TableToDrop = @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop
while @.@.fetch_status = 0
begin
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop
EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_' +
@.TabNoToDrop)
PRINT '_______________________________________
_________'
PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TabNoToDrop + ' Dropped.'
PRINT ''
FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
end
close lcurTables_To_Be_Deleted
deallocate lcurTab|||On Mon, 9 May 2005 02:54:01 -0700, marcmc wrote:

>Hi, I wonder can someone help me.
(snip)
Hi marcmc,
Since you are already aware of the downsides of this design, and
battling management to get it changed, I won't comment on that...
In your code, you use three almost but not quite equally named
variables: @.TabNoToDrop, @.TableNumToDrop and @.TableToDrop. The first and
third seem to server the same purpose (the first is used in the first
FETCH and the third in the FETCH in the WHILE loop). The second includes
only a part of the table name and is calculated from the first after the
first FETCH. Since it is not recalculated after the FETCH in the WHILE
loop, it's value never changes, and you are just attempting to drop the
same table over and over again.

>Begin
> DECLARE lcurTables_To_Be_Deleted CURSOR FOR
> SELECT substring(NAME,28,3) FROM SNAPS_ARC..SYSOBJECTS(nolock) WHERE NAME
>like @.vchArchiveTable_To_Delete and xtype = 'U' order by crdate desc
> OPEN lcurTables_To_Be_Deleted
> FETCH lcurTables_To_Be_Deleted into @.TabNoToDrop
> SELECT @.TableNumToDrop = convert(int, @.TabNoToDrop) -- wk215
> PRINT '1'
> PRINT @.TableNumToDrop
> SELECT @.TableNumToDrop = + convert(varchar,(@.TableNumToDrop - 1))
> PRINT '2' -- wk214
> PRINT @.TableNumToDrop
> while @.@.fetch_status = 0
> begin
> EXEC('DROP TABLE SNAPS_ARC..' + @.vchArchiveTable_To_Delete1 + '_'
>+ @.TableNumToDrop)
> PRINT '_______________________________________
_________'
> PRINT @.vchArchiveTable_To_Delete1 + '_' + @.TableNumToDrop + '
>Dropped.' PRINT ''
> FETCH lcurTables_To_Be_Deleted into @.TableToDrop
> end
> close lcurTables_To_Be_Deleted
> deallocate lcurTables_To_Be_Deleted
>End
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)