为什么我不能在 SQL Management Studio 的开始/结束块中使用“创建架构”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5748056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I use "create schema" in a begin/end block in SQL Management Studio?
提问by Brennan
I generated a script which creates all users and schemas for this database and when I wrap the CREATE statements with an IF EXISTS check I find that it does not allow the CREATE SCHEMA call to run in the BEGIN/END block. It complains that it is invalid syntax. Yet I can run the command on it's own. A sample of the code is below. I am using SQL Server 2008 and Management Studio R2. Why is this invalid syntax?
我生成了一个脚本，它为这个数据库创建所有用户和模式，当我用 IF EXISTS 检查包装 CREATE 语句时，我发现它不允许 CREATE SCHEMA 调用在 BEGIN/END 块中运行。它抱怨它是无效的语法。但是我可以自己运行命令。代码示例如下。我正在使用 SQL Server 2008 和 Management Studio R2。为什么这是无效的语法？
--DROP SCHEMA [acme]
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme'))
BEGIN
CREATE SCHEMA [acme] AUTHORIZATION [dbo]
END
回答by Tom H
Schema creations must be the only statement in a batch. One way to get around it is like so:
模式创建必须是批处理中的唯一语句。绕过它的一种方法是这样的：
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme'))
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
回答by Mark Whitfeld
Here is an even simpler solution (simpler check):
这是一个更简单的解决方案（更简单的检查）：
IF (SCHEMA_ID('acme') IS NULL)
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
回答by Martin Smith
It needs to be in its own batch. You can wrap it in EXEC('')
它需要在自己的批次中。你可以把它包起来EXEC('')
EXEC('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
I believe the reason for the requirement is something to do with an older version of the CREATE SCHEMAsyntax introduced in version 6.5 (at least that's what it says here).
我相信要求的原因与CREATE SCHEMA版本 6.5 中引入的旧版本语法有关（至少它是这样说的）。
回答by Joe Stefanelli
CREATE SCHEMAmust be in it's own batch, so embed it inside an EXECand you should be OK.
CREATE SCHEMA必须在它自己的批次中，因此将其嵌入到 an 中EXEC，您应该没问题。
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'acme'))
BEGIN
EXEC ('CREATE SCHEMA [acme] AUTHORIZATION [dbo]')
END
回答by Zaven Zareyan
Sometimes (always) you're not allowed to use Dynamic SQL, so using EXECis not the best approach. Using GOstatement can do things better:
有时（总是）你不允许使用动态 SQL，所以使用EXEC不是最好的方法。使用GO语句可以做得更好：
USE [MyDB]
GO
IF (SCHEMA_ID('MySchema') IS NOT NULL)
BEGIN
DROP SCHEMA [MySchema];
END
GO
CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]
GO
