Create View' Must Be The First Statement in a Query Batch

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

put GO after PRINT 'Creating Player View' and it should work:

PRINT 'Creating Player View'
GO

CREATE VIEW playerView AS

CREATE VIEW' must be the first statement in a query batch. (despite GO statements)

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.

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.

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

SQL: Why does CREATE VIEW have to be the first statement in a query batch?

About the only valid reason to need this ability is when checking for the existence of an object. This is something that sql server has struggled with because they lack the ability to Create Or Replace like many other databases do. That is how it works in sql server unfortunately.

In SQL Server 2016 they introduced Create Or Alter which deals with this.

As for the reason why this was not introduced earlier this would be a question to ask of the designers of the software which is not really answerable.

SQL Server : dymanic SQL error: 'CREATE VIEW' must be the first statement in a query batch

About GO:

GO is not a Transact-SQL statement; it is a command recognized by the
sqlcmd and osql utilities and SQL Server Management Studio Code
editor.

Split your query into separate requests:

EXEC sp_executesql N'
USE CTR

IF OBJECT_ID(N''[dbo].[two_columns]'', N''V'') IS NOT NULL
    DROP VIEW [dbo].[two_columns];
IF OBJECT_ID(N''[dbo].[count_in_group]'', N''V'') IS NOT NULL
    DROP VIEW [dbo].[count_in_group];
IF OBJECT_ID(N''[dbo].[rank_in_group]'', N''V'') IS NOT NULL
    DROP VIEW [dbo].[rank_in_group];
    IF OBJECT_ID(N''[dbo].[most_frequent_in_group]'', N''V'') IS NOT NULL
    DROP VIEW [dbo].[most_frequent_in_group];'

EXEC sp_executesql N'
CREATE VIEW [dbo].[two_columns] AS
SELECT [hash_vcc] ,[legal_name] FROM [dbo].[ctr_vendor_pay]'

...

And so on.

Why am I getting Create View must be the only statement in the batch in this specific View?

Put a GO on its own line, between the USE [MyDatabase] and the CREATE

CREATE VIEW must be the only statement in the batch

Just as the error says, the CREATE VIEW statement needs to be the only statement in the query batch.

You have two option in this scenario, depending on the functionality you want to achieve:

1. Place the CREATE VIEW query at the beginning

   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   
   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
           where UnitPrice = MinMoney
       )
   

2. Use GO after the CTE and before the CREATE VIEW query

   -- Option #2

   with ExpAndCheapMedicine(MostMoney, MinMoney) as
   (
       select max(unitprice), min(unitprice)
       from Medicine
   )
   ,
   findmostexpensive(nameOfExpensive) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MostMoney
   )
   ,
   findCheapest(nameOfCheapest) as
   (
       select tradename
       from Medicine, ExpAndCheapMedicine
       where UnitPrice = MinMoney
   )
   
   GO    
   
   CREATE VIEW showing
   as
   select tradename, unitprice, GenericFlag
   from Medicine;
   

---

Related Topics