Nested cursors and Updates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Nested cursors and Updates

Author

Topic

Heavyg
Starting Member

3 Posts

Posted - 2004-04-08 : 16:26:46

I am stuck and going blind having looked at this for hours. I use cursors frequently with update statments, but this is the first time i have tried to use nested cursors. I am trying to delete all records for specific clients and matters from all tables. Any Assitance would be great.

Thanks im advance

------------------------------------------------------------

declare @Column as varchar(100)
declare @Table as varchar(100)
declare @Command1 as varchar(700)
declare @Command2 as varchar(700)
declare @client varchar(15)
declare @matter varchar(15)

DECLARE Tables_Fields CURSOR FOR

select sysobjects.name, syscolumns.name
from sysobjects join
(syscolumns join systypes on syscolumns.xtype =systypes.xtype)
on syscolumns.id = sysobjects.id
where
sysobjects.type = 'U'

OPEN Tables_Fields
FETCH NEXT FROM Tables_Fields
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN

-- Declare an inner cursor
DECLARE client_matter CURSOR FOR

Select entityref, number from matters where feeearnerref in ('tfb','HN')--Change the values for each Practice

OPEN client_matter
FETCH NEXT FROM client_matter
INTO @client,@matter
IF @@FETCH_STATUS <> 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @Command2 ='delete from '+ @table + 'where '+ @column + ' = ''' + @client + ''' and ' + @column + ' = ''' + @matter + ''''
Print @Command2
/*EXECUTE (@Command2)*/
FETCH NEXT FROM client_matter
INTO @client,@matter
END
CLOSE client_matter
DEALLOCATE client_matter
-- Get the next author.
FETCH NEXT FROM Tables_Fields
INTO @table, @column
END

CLOSE Tables_Fields
DEALLOCATE Tables_Fields
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-08 : 19:40:14

Why would you want to use a cursor (for this specifically or ever).
Learn to code sql and you will find yourself a lot more proficient at interfacing with databases.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Heavyg
Starting Member

3 Posts

Posted - 2004-04-08 : 19:43:52

Why?
Because im just leaning, am self taught and so far its worked. Probably not the most efficeint, or prettyest method, but all i need to do is for it to work :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-09 : 00:24:10

Are you having any issues with it? I can't think of any way to do what you are doing set-based.

You can change your cursors to loops though. This will save the overhead of the cursors.

DECLARE
@int_counter INT,
@int_max INT

CREATE TABLE #tables_fields(
ident INT IDENTITY(1,1) PRIMARY KEY,
tables VARCHAR(256),
columns VARCHAR(256))

INSERT @tables_fields(
tables,
columns)

SELECT
so.name,
sc.name
FROM
sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.xtype = 'U'

SELECT
@int_counter = 1,
@int_max = (SELECT MAX(ident) FROM #tables_fields)

WHILE @int_counter <=@int_max
BEGIN

--Do whatever you would normally do inside a cursor.

SELECT @int_counter = 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Heavyg
Starting Member

3 Posts

Posted - 2004-04-09 : 03:38:29

Thanks for yor time derrickleggit, the problem i am finding is where normally (in a 1 cursor version) the print statement returns a one line statement which i can the run with the execute command. in this the statement is not generated,the 2 selects do there job and return the values, but I cant sem to get the 4 variables to work in one statement.

I can see what you mean with the loop you sugested, but i am at a loss as to how pass each value into the final statement i need.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-09 : 11:22:01

First of all check the first cursor

select sysobjects.name, syscolumns.name
from sysobjects join
(syscolumns join systypes on syscolumns.xtype =systypes.xtype)
on syscolumns.id = sysobjects.id
where
sysobjects.type = 'U'

I suspect it doesn't work as you expect - especially as xtype is not unique in systypes.

I'm not really sure what you are doing as it will give all tables/columns and I don't think you really want to loop through that.

That cursor could be (look at information_schema views)

select sysobjects.name, syscolumns.name
from sysobjects
join syscolumns on syscolumns.id = sysobjects.id
where sysobjects.type = 'U'

But looping round all columns in all tables is an odd thing to do - especially as the stated aim is to delete all records for specific clients.
Fist of all I would expect something which relates tables and columns to the entity values you are interested in.
The query you have will execute the delete statement for all tables/columns. Ithink you need a table which says table/column holds Client and table/column holds matter. I also think your query will fail when you get to datetime fields.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources