“CREATE VIEW”必须是查询批次中的第一条语句('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'

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.
*/

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

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

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

任何想法，我要去哪里错了吗？

Answer 1:

把GO后PRINT 'Creating Player View' ，它应该工作：

PRINT 'Creating Player View'
GO

CREATE VIEW playerView AS

Answer 2:

批次由单词分隔GO这是客户端工具的指令，而不是到SQL Server，特别是告诉这些工具如何查询分成批次- 。

这个错误告诉你， 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 

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

Answer 3:

把CREATE VIEW代码中EXECUTE

SOME CONDITION..

EXECUTE('CREATE  VIEW vwName...')

Answer 4:

这通常是因为能够创建一个视图或任何DBO，您需要整个脚本是在一个事务中，或者您需要打开SET QUOTED_IDENTIFIER。

即

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

Answer 5:

如果试图执行从实体框架迁移脚本您也可能会遇到这个问题。 我认为，这是由于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
'

果然整个事情到一个单一的命令SQL执行。 这种做法来自这个非常有用的文章使用SQL视图实体框架代码首先由摩根Kamoga 。

文章来源: 'CREATE VIEW' must be the first statement in a query batch