CREATE VIEW must be the only statement in the batch

Question

I'm trying to make a view. So far, I have written this:

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 )  CREATE VIEW showing as select tradename, unitprice, GenericFlag from Medicine; 

Unfortunately, I get an error on the line containing CREATE VIEW showing

"CREATE VIEW must be the only statement in the batch"

How can I fix this?!

Answer 1

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; 

Answer 2

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];');