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?!

I'm trying to understand why you create CTEs that are then not referenced in the view. Are these two separate queries, or are you planning to add the resultsets from the CTE to your view at a later point? — AHiggins, Dec 03 '14 at 13:01

Radu Gheorghiu · Accepted Answer · 2017-07-26T13:53:05.233

26

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:

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
    )

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;

edited Jul 26 '17 at 13:53

answered Dec 03 '14 at 12:52

Radu Gheorghiu

20,049
16
72
107

I think your answer might better fit what the OP is asking for; the fact that the CTEs are not referenced within the SELECT statement might indicate that these are two separate issues. I'm not sure, though, if the OP plans to use these CTEs in the view during a later stage of development, so I'm going to leave my answer here for the moment until the OP responds to my question. – AHiggins Dec 03 '14 at 13:00
@AHiggins Yes, the OP should give a few more details about that. But seeing as in his question the VIEW is **after** the CTE, then I assumed that there's just that small SELECT for the view and the CTE is just some code he also has in the query window (possibly in SSMS) and he's trying to execute all the queries in the window and he gets an error. – Radu Gheorghiu Dec 03 '14 at 13:03
It seems the last suggestion does not work (at least in SQL Server 2014). There is a solution here: http://stackoverflow.com/questions/3133982/why-cant-i-create-a-view-inside-of-a-begin-end-block – Wouter Sep 27 '16 at 10:14
Have you tested this. When I just wrap a create view statement, with BEGIN and END, I still get the error. – Timothy Gonzalez Oct 07 '16 at 21:47
@TimothyGonzalez You should be able to do this with by putting the `GO` statement in before the `CREATE` keyword. – Radu Gheorghiu Oct 07 '16 at 21:57
As @TimothyGonzalez says, option 2.2 does not work at all. – user247702 Jul 26 '17 at 13:49
@Stijn Removed. This is a very old post of mine, I don't know what I was thinking when I wrote this. Thanks for reminding me to take another look at it. I would appreciate the correction in down-votes if that's what it was for. – Radu Gheorghiu Jul 26 '17 at 13:53
1

Thank you for the correction. My vote has been adjusted accordingly :) – user247702 Jul 26 '17 at 14:13
@Stijn Appreciated! – Radu Gheorghiu Jul 26 '17 at 14:14

score 14 · Answer 2 · answered Jan 08 '19 at 09:36

14

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

answered Jan 08 '19 at 09:36

Mozart

2,117
2
20
38

This is better than the "go" solution because it does not break the flow – River Jan 17 '23 at 19:13

AlexB · Answer 3 · 2021-01-28T15:51:53.400

4

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 )

edited Jan 28 '21 at 15:51

answered Jan 25 '21 at 11:02

AlexB

7,302
12
56
74

SAURABH AHALAWAT · Answer 4 · 2022-12-14T06:20:04.530

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

edited Dec 14 '22 at 06:20

answered Dec 14 '22 at 06:13

SAURABH AHALAWAT

47
2

It has worked for me. But can you explain the logic behind this? – Venugopal Bukkala Dec 27 '22 at 07:25

score 1 · Answer 5 · answered Feb 04 '23 at 12:36

1

use statement terminator ; or use GO after cte statement

answered Feb 04 '23 at 12:36

SAURABH AHALAWAT

47
2

score 0 · Answer 6 · answered Jan 27 '23 at 11:16

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

CREATE VIEW must be the only statement in the batch

6 Answers6

Linked