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  SQL Server Development (2000)  The Great Cursor Debate

Author  Topic  Anna Starting Member 20 Posts Posted - 2001-06-21 : 04:48:10 At the risk of seeming over sensitive I seem to be picking up some anti cursor vibes from the forum. I've used cursors for a particular job and am now beginning to have bad feelings about it as everything I read here posted by the 'wise ones' seems to be telling me I've got it all wrong. So here it is - feedback would be appreciated. There's a requirement to insert a copy of a record into a table on update. This applies to a number of tables in my database, and my sp knows which by checking a lookup table. Each of the tables has an autoincrement identity field on them so when I do the insert statement it reads: insert into table1(field2,field3) select field2,field3 from table1 where.... field1 is the identity field so we leave that out. In order to avoid explicitly writing a separate insert statement for each table I've used a cursor in a function to return a variable containing a comma delimited list of fields. The code reads: DECLARE fieldcursor CURSOR for select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = @table_name and ordinal_position <> 1 open fieldcursor set @fieldlist = null fetch next from fieldcursor into @fieldname while @@FETCH_STATUS = 0 begin set @fieldlist = @fieldlist+','+@fieldname fetch next from fieldcursor into @fieldname end close fieldcursor deallocate fieldcursor select @fieldlist = STUFF(@fieldlist,1,1,'') I can then do a dynamic statement: exec('insert into '+@table_name+'('+@fieldlist+') select...) Am I just saving up trouble for myself by doing all this dynamically? Thanks! Anna