SQL “CREATE VIEW”必须是查询批处理中的第一条语句

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

提问by Shivarn

Basically its what the title says. This is my code.

基本上就是标题所说的。这是我的代码。

USE Assignment2;
GO

/* Player View (2 marks)
    Create a view which shows the following details of all players:
        ? The ID number of the player
        ? The first name and surname of the player concatenated and given an alias of “full_name”
        ? The team ID number of the player (if applicable)
        ? The team name of the player (if applicable)
        ? The coach ID number of the player (if applicable)
        ? The name of the player's coach (if applicable)

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that players without teams or coaches are still included in the results.

*/


-- Write your Player View here
PRINT 'Creating Player View'

CREATE VIEW playerView AS 
SELECT player.id, player.firstName + ' ' + player.surname AS 'Full name', player.team, team.name, player.coach, coach.firstName, coach.surname 
FROM player
LEFT OUTER JOIN team
    ON player.team = team.id
    LEFT OUTER JOIN player as coach
        ON player.coach = coach.id;



GO
/* Race View (3 marks)
   Create a view which shows the following details of all races:
        ? All of the columns in the race table
        ? The name of the race type, course and team involved in the race
        ? The full name of the player observing the race and the full name of the MVP (if applicable)
        ? A calculated column with an alias of “unpenalised_score”, which adds the points penalised to the final score

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that races without MVPs are still included in the results.
*/

-- Write your Race View here
PRINT 'Creating Race View'

CREATE VIEW raceView AS 
SELECT race.id, race.dateOfRace, race.raceType, raceType.name AS raceTypeName, race.course, course.name AS courseName, race.team, team.name AS teamName, race.observer, obs.firstName + ' ' + obs.surname AS observer_name, race.mvp, mvp.firstName + ' ' + mvp.surname AS mvp_name, race.pointsPenalised, race.finalScore + race.pointsPenalised AS unpenalised_score, race.finalScore
FROM race
INNER JOIN raceType
    ON race.raceType = raceType.id
    INNER JOIN course
        ON race.course = course.id
        INNER JOIN team
            ON race.team = team.id
            LEFT OUTER JOIN player AS mvp
                ON race.mvp = mvp.id
                LEFT OUTER JOIN player AS obs
                    ON race.observer = obs.id;
GO 

SELECT * 
FROM playerView

SELECT *
FROM raceView


/* Additional Information:
   The views are very convenient replacements for the tables they represent, as they include the names and calculated values that you will often need in queries.
   You are very much encouraged to use the views to simplify the queries that follow.  You can use a view in a SELECT statement in exactly the same way as you can use a table.

   If you wish to create additional views to simplify the queries which follow, include them in this file.
*/

When I run each CREATE VIEWseparately, it seems to run it correctly with no errors. But when I try to run the entire script, it gives me this error.

当我分别运行每个时CREATE VIEW，它似乎可以正确运行而没有错误。但是当我尝试运行整个脚本时，它给了我这个错误。

Msg 111, Level 15, State 1, Line 20
'CREATE VIEW' must be the first statement in a query batch.
Msg 111, Level 15, State 1, Line 15
'CREATE VIEW' must be the first statement in a query batch.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'playerView'.

消息 111，级别 15，状态 1，第 20 行
“CREATE VIEW”必须是查询批处理中的第一条语句。
消息 111，级别 15，状态 1，第 15 行
“CREATE VIEW”必须是查询批处理中的第一条语句。
消息 208，级别 16，状态 1，第 2 行
对象名称“playerView”无效。

Before attempting to run this script, I first delete database, recreate the tables, populate them and then run this script.

在尝试运行此脚本之前，我首先删除数据库，重新创建表，填充它们，然后运行此脚本。

Any ideas where I'm going wrong?

任何想法我哪里出错了？

回答by DiverseAndRemote.com

put GOafter PRINT 'Creating Player View'and it should work:

放在GO后面PRINT 'Creating Player View'，它应该可以工作：

PRINT 'Creating Player View'
GO

CREATE VIEW playerView AS

回答by Damien_The_Unbeliever

Batches are delimited by the word GO- which is an instruction to client tools, not to SQL Server, specifically telling those tools how to split your query into batches.

批处理由单词分隔GO- 这是对客户端工具而非 SQL Server 的指令，特别是告诉这些工具如何将查询拆分为批处理。

The error tells you that CREATE VIEWmust be the first statement in a batch:

该错误告诉您CREATE VIEW必须是批处理中的第一条语句：

USE Assignment2;
GO

/* Player View (2 marks)
    Create a view which shows the following details of all players:
        ? The ID number of the player
        ? The first name and surname of the player concatenated and given an alias of “full_name”
        ? The team ID number of the player (if applicable)
        ? The team name of the player (if applicable)
        ? The coach ID number of the player (if applicable)
        ? The name of the player's coach (if applicable)

   Creating this view requires a select statement using multiple joins and concatenation of names.  
   Make sure that you use the appropriate type of join to ensure that players without teams or coaches are still included in the results.

*/


-- Write your Player View here
PRINT 'Creating Player View'

GO -->-- New GO here

CREATE VIEW playerView AS 

So I've added a GObefore CREATE VIEW

所以我添加了一个GO前CREATE VIEW

回答by kirk

Put the CREATE VIEW Code inside EXECUTE

将 CREATE VIEW 代码放在 EXECUTE 中

SOME CONDITION..

EXECUTE('CREATE  VIEW vwName...')

回答by tomRedox

You may also run into this issue if trying to execute scripts from Entity Framework migrations. I think this is due to EF running those scripts in a transaction (as mentioned in another answer to this question). You can get round that with this type of syntax:

如果尝试从实体框架迁移中执行脚本，您也可能会遇到此问题。我认为这是由于 EF 在事务中运行这些脚本（如该问题的另一个答案中所述）。您可以使用这种类型的语法来解决这个问题：

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MovieActors]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[V_MovieActors]
AS
SELECT       NEWID() AS Id, dbo.Movie.Title, dbo.Movie.ReleaseDate, dbo.Actor.FirstName + '' '' + dbo.Actor.LastName AS Actor, dbo.Actor.DateOfBirth
FROM            dbo.Actor INNER JOIN
                         dbo.Movie ON dbo.Actor.Id = dbo.Movie.Actor_Id
'

Which turns the whole thing into a single command for SQL to execute. That approach comes from this very useful article Using SQL Views With Entity Framework Code First by Morgan Kamoga.

这将整个事情变成了 SQL 执行的单个命令。该方法来自Morgan Kamoga 的这篇非常有用的文章Using SQL Views With Entity Framework Code First。

回答by Fandango68

This usually happens because being able to create a VIEW or any DBO, you require the entire script to be inside a TRANSACTION or you need to turn on SET QUOTED_IDENTIFIER.

这通常是因为能够创建 VIEW 或任何 DBO，您需要整个脚本都在 TRANSACTION 中，或者您需要打开 SET QUOTED_IDENTIFIER。

ie

IE

USE [yourdatabase]
GO

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

...
...

-- Write your Race View here
PRINT 'Creating Race View'
GO

CREATE VIEW raceView AS 
SELECT race.id, race.dateOfRace, race.raceType, raceType.name AS raceTypeName, race.course, course.name AS courseName, race.team, team.name AS teamName, race.observer, obs.firstName + ' ' + obs.surname AS observer_name, race.mvp, mvp.firstName + ' ' + mvp.surname AS mvp_name, race.pointsPenalised, race.finalScore + race.pointsPenalised AS unpenalised_score, race.finalScore
FROM race
INNER JOIN raceType
    ON race.raceType = raceType.id
    INNER JOIN course
        ON race.course = course.id
        INNER JOIN team
            ON race.team = team.id
            LEFT OUTER JOIN player AS mvp
                ON race.mvp = mvp.id
                LEFT OUTER JOIN player AS obs
                    ON race.observer = obs.id;
GO 

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END
GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION
GO

SET NOEXEC OFF