Sql server® 2012 t-sql fundamentals

Statements That Cannot Be Combined in the Same Batch

The following statements cannot be combined with other statements in the same batch: CREATE 

DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE 

TRIGGER, and CREATE VIEW. For example, the following code has an IF statement followed by a 

CREATE VIEW statement in the same batch and therefore is invalid.

IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL DROP VIEW Sales.MyView; 

 

CREATE VIEW Sales.MyView 

AS 

 

SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders 

FROM Sales.Orders 

GROUP BY YEAR(orderdate); 

GO

An attempt to run this code generates the following error.

Msg 111, Level 15, State 1, Line 3 

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

To get around the problem, separate the IF and CREATE VIEW statements into different batches by 

adding a GO command after the IF statement.

www.it-ebooks.info

---

344  

Microsoft SQL Server 2012 T-SQL Fundamentals