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

You can also use :

CREATE VIEW vw_test1 AS SELECT [Name] FROM dbo.test1;
GO
CREATE VIEW vw_test2 AS SELECT [Name] FROM dbo.test2;
GO

--If you need to grant some rights, just use :
GRANT SELECT ON vw_test....

It's easy to understand and avoid dynamic SQL (even if dynamic SQL also works )

Answer 3

Simply use GO before and after creating view it will solve your problem

GO
Create view Vaccinated as
select 
      location,
      population,
      vaccination
from Covid
GO

Answer 4

Funny, my issue, creating this error was a missing comma in the select fields )))

Like this:

CREATE VIEW [dbo].[ITEM_VIEW]
AS
SELECT     dbo.ORDR.one, CAST(dbo.ORDR.two as varchar) AS order_number 
dbo.RDR1.three AS line_number
FROM         dbo.RDR1 INNER JOIN
                  dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry

should be

CREATE VIEW [dbo].[ITEM_VIEW]
AS
SELECT     dbo.ORDR.one, CAST(dbo.ORDR.two as varchar) AS order_number, 
dbo.RDR1.three AS line_number
FROM         dbo.RDR1 INNER JOIN
                  dbo.ORDR ON dbo.RDR1.DocEntry = dbo.ORDR.DocEntry

Answer 5

use statement terminator ; or use GO after cte statement

Answer 6

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