CODEWITHSUNDEEP
c#sql-servert-sqlview
Simin.D. Karbasi
Simin.D. Karbasi

Reputation: 13

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

This is my script in C#: 

exec sp_executesql N'
IF OBJECT_ID(N''RealEstate.vwContract'', N''V'') IS NOT NULL
  DROP VIEW RealEstate.vwContract

CREATE VIEW RealEstate.vwContract
AS
  SELECT RealEstate.Contract.ID .... (Rest of Statement omitted for brevity)

The error shows up:

Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.

Please help me.

Upvotes: 1

Views: 4992

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1063013

The message speaks for itself; the create view must be the first statement - but you can cheat. My create scripts (if I need to run them from ADO.NET, so without GO) tend to look a lot like:

if not exists(select 1 from sys.tables where name='SomeTable')
begin
    exec('create table SomeTable ....blah not shown');
    -- more to add indexing etc
end
if not exists(select 1 from sys.tables where name='SomeOtherTable')
begin
    exec('create table SomeOtherTable ....blah not shown');
    -- more to add indexing etc
end

You can do the same thing with sys.views. Perhaps, untested:

if exists (select 1 from sys.views where name = 'MyView')
    exec ('drop view MyView');
exec ('create view MyView ...blah not shown...');

Upvotes: 5

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22074

Split it into two scripts and run first

IF OBJECT_ID(N''RealEstate.vwContract'', N''V'') IS NOT NULL
   DROP VIEW RealEstate.vwContract

then the rest

Upvotes: 1

Related Questions