SQL Server:USE 数据库优先级问题 [英] SQL Server : USE database precedence issue

问题描述

我在使用 USE 数据库时遇到问题；GO 当语句在 if..else 语句中时.我的猜测是 USE Database;GO 应该用在脚本或 sql 语句的顶部.

I am having issues with using a USE Database; GO when the statement is within an if..else statement. My guess is that the USE Database; GO was suppose to be used at the top of the script or sql statement.

也许有人可以帮助我想出一种不同的方法来解决这个问题，我想要做的是，检查特定数据库是否存在，如果它确实从该特定数据库中删除了特定用户的访问权限.我想这样做的方式如下...

Maybe someone could help me come up with a different way of approaching this, what I am trying to do is, check if a particular database exists if it does drop a particular user access from that specific database. The way I was thinking of doing it is as follows ...

IF EXISTS(select * from sys.databases where name='MyDB')
 BEGIN
   USE MyDB
   GO
   DROP USER [tester]
   .
   .
   .
 END
ELSE
 PRINT 'MyDB database is not available'

我将 sql 脚本切成较小的版本，这样我就不会把这篇文章弄得乱七八糟，所以请注意，在 DROP USER

I chopped the sql script to a smaller version so I don't clutter this post so be aware there are some other logic that has been left out after DROP USER

我尝试了各种方法来检查数据库是否存在，所以每个人都知道试过

EDITED: I have tried various ways to check if the db exists so everyone knows Tried

if db_id('MyDB') is not null
or
if object_id('MyDB', 'U')

但是，它总是先直接use MyDb，我不知道为什么以及如何解决它.

But, it always goes directly to use MyDb first and I don't know why and how to work around it.

推荐答案

如评论中所述，不能将 GO 放在一组相互依赖的 SQL 语句中间，因为:

As noted in the comments, you cannot put GO in the middle of a group of SQL statements that are dependent on each other because:

1. GO 表示一个编译批次的结束和下一个编译批次的开始.大多数语句上下文(如 IF..ELSE)不能跨越 GO.还有，

1. GO indicates the end of one compiled batch and the beginning of the next. Most statement contexts (like an IF..ELSE) cannot span a GO. And,

GO 甚至不是 SQL 语句，它是 Management Studio/SQLCMD 命令，因此在其他任何地方都无法识别.

GO isn't even a SQL statment, it's a Management Studio/SQLCMD command, so it won't be recognized anywhere else.

您的情况是一种常见需求，但没有单一的解决方案.对于您列出的特定情况，使用动态 SQL 可能是最好的方法:

Your situation is a common need, but there's no one single solution. For the specific case that you list, using Dynamic SQL is probably the best approach:

IF EXISTS(select * from sys.databases where name='MyDB')
 BEGIN
   EXEC('
     USE MyDB
     EXEC(''
       DROP USER [tester]
        .
        .
        . 
     '')  
   ')
 END
ELSE
 PRINT 'MyDB database is not available'

这利用了这样一个事实，即每个动态 SQL 执行都构成自己的批处理，以替代 GO 的效果(开始一个新批处理)并隔离 USE 与编译器.当然，它非常笨拙，因为需要在 USE..GO 命令之后双重包装"一些东西.

This leverages the fact that Dynamic SQL exections each constitute their own batch to both substitue for GOs effect (starting a new batch) and to isolate the peculiar interactions of USE with the compiler. It's very kludgy of course because of the need to "double-wrap" things after the USE..GO commands.

还要注意，由于这种双重包装，其中的任何字符串都必须用四重引号引起来.

Note also that because of this double-wrapping any strings inside it will have to be quadruple-quoted.

这篇关于SQL Server:USE 数据库优先级问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！