Create View Must Be the Only Statement in the Batch

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;
   

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

How to avoid create view must be the only statement in the batch error?

Here you need to correct two issues like separation and different names for different views. As the error is giving query for creating a view, stored procedure and function should be in the separate batch.

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

For example:

Create view <YourViewName>
Select * from <YourViewName> -- This will give error.

It is because both statements is in the same batch.

-- Creating pizza view
CREATE VIEW pizza_view1
AS
SELECT 1 AS a --Your actual view statement here.
GO

-- Creating side view
CREATE VIEW side_view2
AS
SELECT 2 AS b --Your actual view statement here.
GO

-- Creating order view
CREATE VIEW order_view3
AS
SELECT 3 AS c --Your actual view statement here.

You can find the Documentation of Go here.

Why CREATE VIEW must be first statement in a batch?

It's not so much that it must be the first statement in the batch, but rather that it must be the only statement in the batch. For the same reason CREATE PROCEDURE, CREATE FUNCTION, etc. all have to be in their own batch ... they need to be compiled independently of other code. One reason is to ensure that anything in the batch created before the object actually exists when it is created, and anything that refers to the object afterward has something to point to. Another is to help prevent this stuff:

-- some other code here

CREATE PROCEDURE dbo.whatever
AS
  -- procedure code here

-- some other code here

We don't want "some other code here" to be included in the definition of the stored procedure. We also wouldn't know what to do with this:

CREATE PROCEDURE dbo.proc1
AS
  -- some code here
-- a comment here
CREATE PROCEDURE dbo.proc2
AS

Since we can legally place comments before CREATE PROCEDURE, how does the parser know which object the comment belongs to?

And for the nit-picky, the same thing applies to views. (Note that a procedure was used in the example simply because a procedure can contain multiple statements while a view can't; and a procedure with multiple statements is far more common than a view with multiple comments.) But we can demonstrate the same type of problem - ask yourself if you don't separate the two views into their own batches, how do you know which view should own comment4?

-- comment1
CREATE VIEW dbo.view1
AS
  -- comment2
  SELECT 
  -- comment3
  * from sys.objects
  -- comment4
CREATE VIEW dbo.view2
AS
  -- comment5
  SELECT 
  -- comment6
  * from sys.objects

In Management Studio, you work around this by placing a GO between object scripts. Note that GO is not T-SQL, it is an app-specific batch separator; from other interfaces, you will need to transmit your batches separately in other ways.

Again, the answer is in the first sentence: a script to create or alter any module in SQL Server - including views, procedures, functions, and triggers - must be the only statement in the batch.

Create VIEW' must be the only statement in the batch

To create a view in a stored procedure, you need to do this in dynamic SQL (especially since the view itself can't take a variable). This is because modules can not be created as part of a larger script (like one that would be required if you have conditional logic, like IF <some condition> CREATE VIEW).

DECLARE @sql nvarchar(max) = N'CREATE VIEW dbo.tblList 
    AS
      SELECT ItemOrder, DisplayVal, 
        SomeAlias = DATEADD(DAY, DaysDue, ''' 
          + CONVERT(char(8), @PDate, 112)
          + N''') FROM dbo.tblLine WHERE ID = 1;';

EXEC sys.sp_executesql @sql;

But once you call this stored procedure a second time, it's going to fail, because you are trying to create a view named dbo.tblList and that view already exists. Perhaps you can elaborate on what you're trying to, at a higher level than "I want to create a view in a stored procedure."

---

Related Topics