IF EXISTS 在 SQL Server 游标中不起作用

发布于 2024-12-02 11:41:32 字数 399 浏览 1 评论 0原文

我有一个工作正常的光标，但是当它到达脚本的这一部分时，即使该表不存在，它似乎仍然运行更新：

 SET @sql = 'IF (EXISTS (SELECT * FROM ps_vars_' + @datasetid + '))
             BEGIN
     UPDATE ps_vars_' + @datasetid + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'')
         END';

  EXEC SP_EXECUTESQL @sql

我缺少什么？ #datasetid 变量也正确传入。

原文

I have a cursor which works fine but when it gets to this part of the script, it seems to still run the update even though the table doesn't exists:

 SET @sql = 'IF (EXISTS (SELECT * FROM ps_vars_' + @datasetid + '))
             BEGIN
     UPDATE ps_vars_' + @datasetid + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'')
         END';

  EXEC SP_EXECUTESQL @sql

What am I missing? The #datasetid variable gets passed in correctly too.

分享到QQ

分享到微博

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

发布评论

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

双马尾 2024-12-09 11:41:32

DECLARE @tablename sysname 

SET @tablename = 'ps_vars' + @datasetid

IF (OBJECT_ID(@tablename, 'U') IS NOT NULL)
BEGIN
 SET @sql = ' UPDATE ' + QUOTENAME(@tablename) + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'') ';
     EXEC sp_executesql @sql  
END

DECLARE @tablename sysname 

SET @tablename = 'ps_vars' + @datasetid

IF (OBJECT_ID(@tablename, 'U') IS NOT NULL)
BEGIN
 SET @sql = ' UPDATE ' + QUOTENAME(@tablename) + '
     SET    programming_notes = replace(programming_notes, ''Some of the variables listed are source variables.'') ';
     EXEC sp_executesql @sql  
END

回复收藏 0 原文

贱人配狗天长地久 2024-12-09 11:41:32

当您将 EXISTS 与表名一起使用来查看该表是否存在时，您实际上是在尝试访问该表 - 该表不存在。这就是您收到错误的原因，而不是因为您的 UPDATE 语句。

尝试这样做：

SET @sql = 'IF (OBJECT_ID(''ps_vars_' + @datasetid + ''') IS NOT NULL)
    BEGIN
        UPDATE ...
    END'

然后考虑一下您的数据库设计可能存在什么问题，需要您使用这样的动态 SQL。也许你的设计正是它需要的那样，但根据我的经验，十分之九（可能更多）这种代码是糟糕设计的症状。

When you use the EXISTS with the table name to see if the table exists you're actually trying to access the table - which doesn't exist. That's why you're getting an error, not because of your UPDATE statement.

Try this instead:

SET @sql = 'IF (OBJECT_ID(''ps_vars_' + @datasetid + ''') IS NOT NULL)
    BEGIN
        UPDATE ...
    END'

Then think about what might be wrong with your database design that requires you to use dynamic SQL like this. Maybe your design is exactly how it needs to be, but in my experience 9 out of 10 times (probably much more) this kind of code is a symptom of a poor design.

回复收藏 0 原文

~没有更多了~