CREATE VIEW Must Be The Only Statement In The Batch

Answer :

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;
   

I came across this question when I was trying to create a couple of views within the same statement, what worked well for me is using dynamic SQL.

    EXEC('CREATE VIEW V1 as SELECT * FROM [T1];');
    EXEC('CREATE VIEW V2 as SELECT * FROM [T2];');