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

Answer 3

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.

Answer 4

Working & easy solution:

Just wrap your sql with EXEC keyword. Note that you need only to give quotes once, even in multiline sql:

IF NOT EXISTS(select * FROM sys.views where name = 'TestView')
  BEGIN    
    EXEC ('
      CREATE VIEW [dbo].[TestView]
      AS
        SELECT
          *
        FROM
          dbo.SomeTable
    ')
  END
ELSE
  BEGIN
    EXEC ('
      ALTER VIEW [dbo].[TestView]
      AS
        SELECT
          *
        FROM
          dbo.SomeTable
    ')
  END

Answer 5

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 6

Here's a way to do it that avoids sp_execute_sql:

    IF EXISTS ( SELECT  1
                FROM    sysobjects
                WHERE   type = 'V'
                        AND name = 'vwTradeEventTemp' )
        BEGIN
            DROP VIEW [dbo].[vwTradeEventTemp];
        END;
    GO

    CREATE VIEW [dbo].[vwTradeEventTemp]
    WITH SCHEMABINDING
    AS
    select col1, col2 from table
IF NOT EXISTS ( SELECT  1
                FROM    sysobjects
                WHERE   type = 'V'
                        AND name = 'vwTradeEvent' )
        BEGIN
            PRINT 'vwTradeEvent does not exist, renaming vwTradeEventTemp to vwTradeEvent to work around not being able to check for view existence before creating';
            EXEC sp_rename @objname = 'vwTradeEventTemp',
                @newname = 'vwTradeEvent', @objtype = 'object';
        END;
    ELSE
        BEGIN
            PRINT 'vwTradeEvent already exists, dropping vwTradeEventTemp';
            DROP VIEW dbo.vwTradeEventTemp;
        END;

I don't like using sp_executesql as you lose the syntax highlighting and parsing also doesn't work... This is also handy as it avoids dropping the view. In my case, the view is used in replication so dropping it fails unless replication is dropped and recreated.

Answer 7

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