Create view должна быть первой инструкцией в пакетном запросе

In SQL Server, a batch is a group of one or more T-SQL statements sent at the same time from an application to SQL Server for execution.

If you encounter an error like this:

Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

It’s probably because you’re combining the statement with other statements in the same batch, which is not allowed in batches.

The first part of the error message will depend on the actual statement that you’re using in your batch. In my case it’s CREATE VIEW, but it could just as easily be CREATE PROCEDURE, CREATE FUNCTION, etc if those are the statements you’re using.

Example

Here’s an example of some code that would cause this error:

DROP VIEW IF EXISTS vAllCustomers;

CREATE VIEW vAllCustomers AS
SELECT * FROM Customers;

Result:

Msg 111, Level 15, State 1, Line 3
'CREATE VIEW' must be the first statement in a query batch.

In my case, I’m trying to run two statements; a DROP VIEW statement and a CREATE VIEW statement.

The rules of a T-SQL batch state that the CREATE VIEW statement cannot be combined with other statements in the same batch.

In other words, CREATE VIEW can be the only statement in its batch.

How to Fix the Error

We can fix the above error by simply adding a batch separator after the first statement.

In SQL Server, the GO keyword signals the end of a batch. More specifically, SQL Server utilities interpret GO as a signal that they should send the current batch of T-SQL statements to an instance of SQL Server.

So we could change the previous statement to this:

DROP VIEW IF EXISTS vAllCustomers;
GO
CREATE VIEW vAllCustomers AS
SELECT * FROM Customers;
GO

Adding GO fixes the issue by separating the statements into two separate batches.

Note that GO is not actually part of T-SQL. It’s a command that is recognised by SQL Server utilities for the purpose of separating statements into batches.

You may be able to change the batch separator, depending on the tool you use to connect to SQL Server. For example, in SSMS, you can find this option by going to: Tools > Options > Query Execution > SQL Server and look for an option that says something like “Specify a word or character that can be used to separate batches”.

Problem

When you’re executing a CREATE/ALTER statement to create a procedure/view/function/trigger, you get one of the following errors:

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

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

‘CREATE FUNCTION’ must be the first statement in a query batch.

‘CREATE TRIGGER’ must be the first statement in a query batch.

You can’t execute these CREATE/ALTER statements with other statements.

Solution

The solution is to execute the CREATE/ALTER statement separately from other statements. How you do that depends on if you’re using SSMS/sqlcmd/osql or executing from C#.

If you’re executing from SSMS (or sqlcmd/osql)

Add the keyword GO right before CREATE statement. This is the default batch separator in SSMS. It splits the query into multiple batches. In other words, it executes the CREATE statement by itself in its own batch, therefore solving the problem of it needing to be the first statement in a batch. Here’s an example:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].spGetAllPosts
GO
CREATE PROCEDURE [dbo].spGetAllPosts
AS
BEGIN
    SELECT * FROM Posts
END
Code language: SQL (Structured Query Language) (sql)

If you’re executing from C#

You can’t use the GO keyword in C#. Instead you have to execute the two queries separately. The best way to do that is to execute the first part, then change the CommandText and execute the second part.

using System.Data.SqlClient;

string dropProcQuery = 
	@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spGetAllPosts') AND type in (N'P', N'PC'))
		DROP PROCEDURE[dbo].spGetAllPosts";
		
string createProcQuery = 
	@"CREATE PROCEDURE [dbo].spGetAllPosts
	AS
	BEGIN
		SELECT * FROM Posts
	END";
	
using (var con = new SqlConnection(ConnectionString))
{
	using (var cmd = new SqlCommand(dropProcQuery, con))
	{
	        //Execute the first statement
		con.Open();
		cmd.ExecuteNonQuery();
		
		//Then execute the CREATE statement
		cmd.CommandText = createProcQuery;
		cmd.ExecuteNonQuery();
	}
}

Code language: C# (cs)

В основном это то, что говорится в названии. Это мой код.

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 отдельно, он, кажется, запускает его правильно, без ошибок. Но когда я пытаюсь запустить весь script, он дает мне эту ошибку.

Msg 111, уровень 15, состояние 1, строка 20
«CREATE VIEW» должен быть первым оператором в пакете запросов.
Msg 111, уровень 15, состояние 1, строка 15
«CREATE VIEW» должен быть первым оператором в пакете запросов.
Msg 208, уровень 16, состояние 1, строка 2
Недопустимое имя объекта ‘playerView’.

Прежде чем пытаться запустить этот script, я сначала удаляю базу данных, воссоздаю таблицы, заполняю их, а затем запускаю этот script.

Любые идеи, в которых я ошибаюсь?