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

GO

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

I was going to comment on the answer from ProfK, but couldn't figure out how to format for code in a comment, so here it is an an answer.

---

Using sp_executesql is better, but don't you need to indicate it's an nvarchar? Otherwise I get this error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Here's what I use as a view template:

If not exists (Select Table_Name from INFORMATION_SCHEMA.VIEWS where Table_Name = 'vMessage') begin 
    exec sp_executesql N'create view vMessage as select test = 1'
    print 'Creating view vMessage'
end 

print 'Altering view vMessage'
go
Alter view vMessage 
as
Select 
* 
from Message