How can I avoid using CURSOR - 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)

How can I avoid using CURSOR

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2004-08-06 : 19:39:30

In searching this forum for help in developing the below procedure, I found many, many statements that cursors should be avoided at all costs.

How can I write the below code differently to avoid using a cursor?

I need to find all tables that have a column with a specific name, and then UPDATE each of these tables.

TIA for your help.

==============================================


DECLARE	@sTable		varchar(48),
		@iOldParPK	int,
		@iNewParPK	int,
		@sCmd		varchar(1000),
		@sColName	varchar(48)

SET @sColName = 'Participant_FK'

SET @iOldParPK	= 111
SET @iNewParPK	= 999

DECLARE curTables CURSOR 
FOR
SELECT 	SUBSTRING(so.name, 1, 50) 'Table Name',
		SUBSTRING(sc.name, 1, 50) 'Column Name'
	FROM sysobjects so
	INNER JOIN syscolumns sc ON so.id = sc.id
	WHERE so.type = 'U' 
		AND sc.name LIKE @sColName
	ORDER BY so.name, sc.name

OPEN curTables

FETCH NEXT FROM curTables INTO @sTable, @sColName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN

		SET @sCmd = 'UPDATE ' + @sTable
			+ ' SET '   + @sColName + ' = ' + CAST(@iNewParPK AS varchar(6))
			+ ' WHERE ' + @sColName + ' = ' + CAST(@iOldParPK AS varchar(6))
		
		PRINT @sCmd
                EXEC(@sCmd)
	END
	FETCH NEXT FROM curTables INTO @sTable, @sColName
END

CLOSE curTables
DEALLOCATE curTables

==============================================

BTW, this is a procedure that will be used very infrequently, and only by the DBA. So one would think there might be a consideration of development time vs. run time. Is avoiding using a cursor really the best overall solution in this case?

Best Regards,
Jim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-06 : 22:17:09

well you could use a while loop instead of a cursor, but i don't know if it would matter at that point

Corey

Apollois
Starting Member

49 Posts

Posted - 2004-08-13 : 01:10:35

Have the great cursor-slayers been stumpted? :-)

It's been over a week, and no one has offered an alternative to cursor (sorry Corey, I don't understand your comment).

Might this be a case where cursor should be used?

Best Regards,
Jim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-13 : 05:31:00

well. you could build a single string of update clauses for each table separated by semicolons(;) and then run exec(statement)...

like exec('UpdateString1;UpdateString2;UpdateString3;UpdateString4')

that doesn't need cursors...

Go with the flow & have fun! Else fight the flow :)

Apollois
Starting Member

49 Posts

Posted - 2004-08-13 : 05:45:14

spirit1,

How do you build the string without using a cursor?

Best Regards,
Jim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-13 : 05:49:22

play with this:

Declare @UpdateList varchar(1000)
SELECT @UpdateList = COALESCE(@UpdateList + '; ', '') + 'Update TableName SET ' + column_name + '=value'
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'
select @UpdateList

if you get it to work the way you want post the results, because i'm interested how you'll do it.

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-13 : 07:35:08

this is probably what you need:

Declare @UpdateList varchar(1000)
SELECT @UpdateList = COALESCE(@UpdateList + '; ', '') +
'Update ' + SUBSTRING(so.name, 1, 50) +
' SET ' + SUBSTRING(sc.name, 1, 50) + '=' + CAST(@iNewParPK AS varchar(6)) +
' WHERE '+ SUBSTRING(sc.name, 1, 50) + '=' + CAST(@iOldParPK AS varchar(6))
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.type = 'U'
AND sc.name LIKE @sColName
ORDER BY so.name, sc.name
select @UpdateList
exec(@UpdateList)

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-15 : 08:11:34

For these "DBA ONLY" type thingies I write scripts that output the SQL for the UPDATE, or whatever, and then I cut&paste that to Query Analyser and run it.

That way:

a) I don't need a cursor :-)
b) I can give it the once-over before it runs

Of course for something that runs daily I would build it automated, but.

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-16 : 08:42:21

OH... what about that xp that executes each record of a resultset (assuming the resultset is a set of sql statements...)

I forgot what its called... its one of the sp_executesql type things...

Corey

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-16 : 18:54:55

quote:
Originally posted by Kristen

For these "DBA ONLY" type thingies I write scripts that output the SQL for the UPDATE, or whatever, and then I cut&paste that to Query Analyser and run it.

That way:

a) I don't need a cursor :-)
b) I can give it the once-over before it runs

Of course for something that runs daily I would build it automated, but.

Kristen

Hi Kristen,
Can you provide any sample script.

Thanks

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-08-17 : 06:53:23

DECLARE @sTable varchar(48),
@iOldParPK int,
@iNewParPK int,
@sCmd varchar(1000),
@sColName varchar(48)

DECLARE @ID int
set @ID = 0

SET @sColName = 'Participant_FK'

SET @iOldParPK = 111
SET @iNewParPK = 999

WHILE (1=1)
BEGIN
set rowcount 1
SELECT @id = so.id
@sTable = SUBSTRING(so.name, 1, 50) 'Table Name',
@sColName = SUBSTRING(sc.name, 1, 50) 'Column Name'
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.type = 'U'
AND sc.name LIKE @sColName
and so.id > @id
ORDER BY so.id

If @@rowcount = 0 break
set rowcount 0

SET @sCmd = 'UPDATE ' + @sTable
+ ' SET ' + @sColName + ' = ' + CAST(@iNewParPK AS varchar(6))
+ ' WHERE ' + @sColName + ' = ' + CAST(@iOldParPK AS varchar(6))

PRINT @sCmd
EXEC(@sCmd)
END
END

Hemanth Gorijala
BI Architect / DBA

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 07:07:30

"Can you provide any sample script"

Here's one that will truncate all the tables in a database. I get the chance to see which tables are going to be truncated! Note that there are some commented out bits for indicates tables to be included / excluded

Run using TEXT RESULTS in Query Anayser (not GRID output), and cut & paste to the code window to run.


SELECT 'TRUNCATE TABLE ' + db_name() + '.dbo.[' + name + ']',
	CHAR(13)+CHAR(10)+'GO'
FROM	sysobjects 
WHERE	    type = 'U' 
	AND name NOT IN ('dtproperties')
--	AND name LIKE '%SomeName%'
--	AND name NOT LIKE '%SomeName%'
--	AND name NOT IN ('XXX', 'YYY')
ORDER BY [name]

Kristen

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-17 : 18:45:25

Thanks Kristen!

Apollois
Starting Member

49 Posts

Posted - 2004-08-17 : 21:15:56

Thanks to everyone who has posted!!

Looks like there are least two good candidates to evaluate as an alternative to using cursors. It may take me a few days to test and eval these. As soon as I'm finished, I'll post my findings.

Thanks again.

Best Regards,
Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources