在SQL Server中，有没有办法避免使用Cursor？

I have a table where each record has a Table_Name (name of a table). I then use a Cursor to select all table names related to some record in to a Cursor. Then I do a WHILE for each table name in the Cursor to do some job.

我有一个表，其中每个记录都有一个Table_Name（表的名称）。然后，我使用Cursor选择与Cursor中的某些记录相关的所有表名。然后我为Cursor中的每个表名做一个WHILE来做一些工作。

I want to know if it's possible to solve this problem without using a Cursor.

我想知道是否可以在不使用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

Foreach table name in the cursor I do a dynamic SQL query like this:

游标中的Foreach表名我做了一个动态SQL查询，如下所示：

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

My question is this: Is it possible to avoid using Cursor to solve this problem?

我的问题是：是否可以避免使用Cursor来解决这个问题？

Unfortunately the design of having table name to reference a table can't be changed, of which I would have done immediately if I could.

不幸的是，无法更改具有引用表的表名的设计，如果可以的话，我会立即做的。

3 个解决方案

#1

yes, you can solve this problem without using cursor. Instead you need to introduce the new table which stores the table name from actual table along with auto generated id column.

是的，您可以在不使用游标的情况下解决此问题。相反，您需要引入新表，该表存储来自实际表的表名以及自动生成的id列。

Check out the below sample query

查看以下示例查询

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 above query, you can use your query to populate the 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

#2

Yes, you could avoid the cursor, but you can't avoid the dynamic queries.

是的，您可以避开光标，但无法避免动态查询。

You could possibly make a query that returns all the dynamic queries concatenated together as a single string. That way you could execute them all without using a loop, but that's not really any better...

您可以创建一个查询，将所有连接在一起的动态查询作为单个字符串返回。这样你就可以在不使用循环的情况下执行它们，但这并不是更好......

If you can't change the database design, you are stuck with dynamic queries.

如果无法更改数据库设计，则会遇到动态查询。

#3

Well, you can hide the use of a cursor by using the (undocumented, but widely-used) MS stored procedure sp_MSforeachdb (Google has lots of examples); but that uses a cursor internally, so if it's a philosophical objection then that doesn't really help.

好吧，你可以使用（未记录但广泛使用的）MS存储过程sp_MSforeachdb隐藏游标的使用（谷歌有很多例子）;但是它在内部使用了一个光标，所以如果它是一个哲学上的反对意见，那么这并没有真正帮助。

I don't think there can be a set-based way to do this kind of thing, since each table probably has a different relational structure.

我不认为可以有基于集合的方式来做这种事情，因为每个表可能有不同的关系结构。

#1