Can't use IF for create or alter view depending on view's existense.

Question

I'm trying to achieve what the code below suggests, but I'm getting the error Incorrect syntax near the keyword 'view' on both the create and alter lines.

IF Object_ID('TestView') IS NULL
 BEGIN
  create view TestView
  as
  . . .
 END
ELSE
 BEGIN
  ALTER view TestView
  as
  . . . 
 END

Answer 1

Because ALTER/CREATE commands can't be within BEGIN/END blocks. You need to test for existence and the drop it before doing a create

IF Object_ID('TestView') IS NOT NULL
    DROP VIEW TestView

CREATE VIEW TestView
   as
   . . .

GO

If you are woried about the permissions being lost you can script the GRANT statements as well and re-run those at the end.

You could wrap the create/alter into a string and do an EXEC - that might get ugly for large views

DECLARE @SQL as varchar(4000)

-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE' 

IF Object_ID('TestView') IS NULL
    SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW TestView AS ' + @SQL

EXEC(@SQL)

Answer 2

You should drop the view if it's created then only do an alter

IF OBJECT_ID('TestView') IS NOT NULL
BEGIN 

DROP VIEW TestView
END

GO

CREATE VIEW TestView

AS 

SELECT * FROM TestTable

Answer 3

If you read the rest of the error message it will point out that create view has to be the first statement or something along those lines.

Try:

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[testView]'))
    DROP VIEW [dbo].[testView]
GO

CREATE VIEW [dbo].[testView]
AS

Answer 4

An esteemed colleague helped me on this:

if object_id('demoView') is null
    sp_executesql 'create view demoView as select * from demoTable'

works just fine.