In SQL Server, is there a way to avoid using a cursor?

Question

I have a table where each record has a Table_Name (table name). Then I use the cursor to select all the table names associated with some record in the Cursor. Then I do a WHILE for each table name in Cursor to do some work.

I want to know if this problem can be solved without using a cursor.

DECLARE tables_cursor CURSOR FAST_FORWARD FOR SELECT Table_Name FROM Some_Table WHERE ... FETCH NEXT FROM tables_cursor INTO @Dynamic_Table_Name WHILE @@FETCH_STATUS = 0 BEGIN ... END 

The name of the Foreach table in the cursor. I am executing a dynamic SQL query as follows:

 SELECT @sql = ' UPDATE dbo.' + @Dynamic_Table_Name + ' SET ...' EXEC sp_executesql @sql, @params, ... 

My question is this: is it possible to avoid using the cursor to solve this problem?

Unfortunately, the design with the table name to refer to the table cannot be changed, and I would do it immediately if I could.

Answer 1

Yes , you can solve this problem without using a cursor. Instead, you need to enter a new table that stores the table name from the actual table along with the auto-generated identity column.

Check the example request below

 declare @test table (id int identity,tableName varchar(20)) insert into @test select 'abc' union all select '123' union all select '345' union all select 'sdf' union all select 'uhyi' 

instead of a query, you can use your query to populate a table variable

 insert into @test SELECT Table_Name FROM Some_Table WHERE ... 

and

 --select * from @test declare @cnt int declare @incr int select @cnt = count(id) from @test set @incr = 1 while (@incr <= @cnt) begin select tableName from @test where id = @incr set @incr =@incr + 1 end 

Answer 2

Yes, you could escape the cursor, but you cannot escape dynamic queries.

Perhaps you can make a query that returns all dynamic queries merged together as one row. That way you can do them all without using a loop, but this is not really better ...

If you cannot change the design of the database, you are stuck in dynamic queries.

Answer 3

Well, you can hide the use of the cursor using the (undocumented but widely used) MS stored procedure sp_MSforeachdb (there are many examples on Google); but it uses the cursor inside, therefore, if it is a philosophical objection, then it really does not help.

I don’t think there could be a set-based way to do this, since each table probably has a different relational structure.