'CREATE VIEW'必须是查询批处理中的第一个语句。 （尽管GO声明）

Background

To keep the views up to date, I want to keep each view as a .sql file within a Views folder. On the Seed() method, I am iterating through those files and executing the SQL.

为了使视图保持最新，我希望将每个视图保留为Views文件夹中的.sql文件。在Seed（）方法中，我正在迭代这些文件并执行SQL。

I would like this SQL to drop and recreate the views.

我希望这个SQL删除并重新创建视图。

The Setup

A view file called V_DW_PrepaymentReport.sql:

一个名为V_DW_PrepaymentReport.sql的视图文件：

if exists(select 1 from sys.views where name='V_DW_PrepaymentReport' and type='V')
DROP VIEW V_DW_PrepaymentReport

GO

create view V_DW_PrepaymentReport as

...(other SQL here to finish creating the view)

The seed method code (less relevant, but just FYI):

种子方法代码（不太相关，但只是FYI）：

    private void RegenerateDatabaseViews(RsDatabase context)
    {

        var viewsDirectory = $"{AppDomain.CurrentDomain.BaseDirectory}Views";

        var allViewFiles = Directory.GetFiles(viewsDirectory, "*.sql").OrderBy(x=>x);

        if (!allViewFiles.Any()) { throw new Exception($"No view files found in {viewsDirectory}");}

        foreach (var viewFile in allViewFiles)
        {
            context.Database.ExecuteSqlCommand(File.ReadAllText(viewFile));
        }
    }

The Problem

When running Update-Database, upon hitting the Seed method and running the file, I see:

运行Update-Database时，在点击Seed方法并运行文件时，我看到：

Incorrect syntax near 'GO'.

'GO'附近的语法不正确。

'CREATE VIEW' must be the first statement in a query batch.

'CREATE VIEW'必须是查询批处理中的第一个语句。

The Question

Why do I see this error, despite using a GO statement between batches? Is SQL server ignoring that keyword and considering the whole file to be one batch regardless?

尽管在批次之间使用GO语句，为什么我会看到此错误？ SQL服务器是否忽略该关键字并将整个文件视为一个批处理？

1 个解决方案

#1

GO commands are commands for client tools, not for SQL Server. Each client tool will split the text into batches and submit each batch separately to the server.

GO命令是客户端工具的命令，而不是SQL Server的命令。每个客户端工具都会将文本拆分为批次，并将每个批次分别提交给服务器。

Well, guess what - you're now taking SQL scripts and want the GO behaviour - so you're now implementing a client tool and it's up to you to implement that same behaviour.

好吧，猜猜看 - 你现在正在使用SQL脚本并想要GO行为 - 所以你现在正在实现一个客户端工具，由你来实现同样的行为。

Also, DROP VIEW IF EXISTS doesn't exist in T-SQL.

此外，T-SQL中不存在DROP VIEW IF EXISTS。