[SOLVED] USE statement before CREATE VIEW

USE statement before CREATE VIEW

I am reading a chapter on utilizing views and am unable to get this query to work:

USE [jm0235242]
CREATE VIEW InvoiceBasic 
AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

It has issues with my USE statement:

'CREATE VIEW' must be the first statement in a query batch".

How am I supposed to specify which database it is supposed to look at if I cannot use my USE statement?

Solution

You need to make sure that the CREATE VIEW is the first statement in a SQL batch - as the error clearly explains. So use this:

USE [jm0235242]
GO      --  put that "GO" separator here! 

CREATE VIEW InvoiceBasic 
AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

When you run this in SQL Server Management Studio, the GO keyword ends a batch and executes it - and the CREATE VIEW is now the first statement in the second batch, and it's happy and works, too

Update: the question why exactly the CREATE VIEW (and also ALTER VIEW, for that matter) must be the first statement of a batch eludes me - I've never heard any compelling explanation for this.