GO，T-SQL EXEC()附近的语法不正确 [英] Incorrect Syntax Near GO, T-SQL EXEC()

问题描述

我正在使用以下脚本:

DECLARE @dbName NVARCHAR(20) = 'ABC';
EXEC ( N' USE ' + @dbName + '
GO

-- Create Schema
CREATE SCHEMA meta
GO

-- Create Log Table
CREATE TABLE meta.LogAudit(
[EventDate] [datetime] NOT NULL DEFAULT (getdate()),
[EventType] [nvarchar](64) NULL
)
GO
'
);

但这会引发以下错误.

Msg 111, Level 15, State 1, Line 5
'CREATE SCHEMA' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'GO'.

那是为什么?

-

这个答案似乎正在回答我的问题: 动态SQL错误: "CREATE TRIGGER"必须是查询批处理中的第一条语句

This answer seems to be answering my question: dynamic sql error: 'CREATE TRIGGER' must be the first statement in a query batch

因此，在我看来，我无法动态对其进行编程.我的整个代码按以下方式工作:

So it seems that in my situation I cannot program it dynamically. My whole code works in the following way:

USE DB
GO

CREATE SCHEMA SCH
GO

CREATE TABLE SCH.TABLE
GO

CREATE TRIGGER TRG
GO

因此，由于SCHEMA和TRIGGER必须是第一个查询语句，因此不能以这种方式编写.

So as SCHEMA and TRIGGER needs to be first query statement, it cannot be written in this way.

推荐答案

尝试在[EventType] [nvarchar](64) NULL,之后删除逗号，并查看错误消息是否更改.

Try removing comma after [EventType] [nvarchar](64) NULL, and see if the error message changes.

所以您有2个问题:

1. 正如@Tanner指出的那样，您不能在动态SQL中使用GO.
2. 在meta.LogAudit表列定义中，您仍然具有结尾逗号.

1. As @Tanner has pointed out, you cannot use GO in dynamic SQL.
2. You still have that trailing comma in the meta.LogAudit table columns definition.

尝试运行以下代码:

DECLARE @dbName NVARCHAR(20) = 'ABC';
declare @sql nvarchar(max) = N'exec '+ @DBName + '..sp_executesql N''CREATE SCHEMA meta'''
execute(@sql)
declare @sql2 nvarchar(max) = '
-- Create Log Table
CREATE TABLE '+ @DBName + '.meta.LogAudit(
[EventDate] [datetime] NOT NULL DEFAULT (getdate()),
[EventType] [nvarchar](64) NULL
)'
exec sp_executesql @sql2,N''

与使用当前数据库相反，它将允许您以编程方式在指定的数据库中创建架构.

It will allow you to programmatically create schema in the specified Database as opposite to using current database.

这篇关于GO，T-SQL EXEC()附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！