如何通过游标对多个表执行逻辑？（表名在光标处）

发布于 2024-10-04 13:04:04 字数 646 浏览 2 评论 0原文

我感觉这是非常基本的数据库工作，但它不适合我。我正在尝试从系统表中获取所有逻辑删除表的列表，并将结果存储在游标中。然后，我尝试对每个我遇到困难的表执行一些逻辑。

任何帮助将不胜感激。

这是我得到的错误：必须声明表变量“@tablename”

以下是代码：

declare tombstonetables cursor for 
(select name from sys.objects
where 
name like'%tombstone%'
and type = 'U'--for user_table
)

Print 'Begin purging tombstone tables'

declare @tablename varchar(250)
open tombstonetables

fetch next from tombstonetables into @tablename

WHILE @@FETCH_STATUS = 0
begin
select * from @tablename--real logic goes here later

fetch next from tombstonetables into @tablename

end
close tombstonetables

deallocate tombstonetables

原文

I get the feeling this is pretty basic database work, but it isn't for me. I'm trying to get a list of all of my tombstone tables from system tables and store the results in a cursor. I'm then trying to perform some logic on each of those tables I'm having trouble doing so.

Any help would be greatly appreciated.

Here is the error I get:
Must declare the table variable "@tablename"

Here is the code:

declare tombstonetables cursor for 
(select name from sys.objects
where 
name like'%tombstone%'
and type = 'U'--for user_table
)

Print 'Begin purging tombstone tables'

declare @tablename varchar(250)
open tombstonetables

fetch next from tombstonetables into @tablename

WHILE @@FETCH_STATUS = 0
begin
select * from @tablename--real logic goes here later

fetch next from tombstonetables into @tablename

end
close tombstonetables

deallocate tombstonetables

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮华 2024-10-11 13:04:04

看来您需要使用动态 SQL

这里是一个简单演练的参考 http:// www.mssqltips.com/tip.asp?tip=1160

您可能需要使用sp_executesql

这是一个在您的示例中使用动态 SQL 的简单示例

DECLARE @DynamicSQL nvarchar(100)

WHILE @@FETCH_STATUS = 0
begin
SET @DynamicSQL = 'select * from ' + @tablename --real logic goes here later
EXEC @DynamicSQL
fetch next from tombstonetables into @tablename

end

Looks like you need to use Dynamic SQL

Here is a reference to a simple walk through http://www.mssqltips.com/tip.asp?tip=1160

You will probably need to make use of sp_executesql

Here is a simple example of using Dynamic SQL with your example

DECLARE @DynamicSQL nvarchar(100)

WHILE @@FETCH_STATUS = 0
begin
SET @DynamicSQL = 'select * from ' + @tablename --real logic goes here later
EXEC @DynamicSQL
fetch next from tombstonetables into @tablename

end

回复收藏 0 原文

~没有更多了~