How to check if a view exists and create if it does not

Question

I want to create a view if not exists in SQL Server 2016

IF EXISTS(SELECT 1 FROM sys.views 
     WHERE Name = 'VI_ALL_CITIES_AS_CATEGORY')
BEGIN
CREATE VIEW VI_ALL_CITIES_AS_CATEGORY AS
    SELECT PERSONS.FIRST_NAME AS 'Име', PERSONS.LAST_NAME AS 'Фамилия', CITIES.CITY_NAME AS 'Град'
    FROM CITIES
    LEFT JOIN PERSONS ON CITIES.ID = PERSONS.CITY_ID ;
END

But it gives me error:

Incorrect syntax: 'CREATE VIEW' must be the only statement in the batch.

Answer 1

Try this approach

IF NOT EXISTS
(
    SELECT 1
    FROM sys.views
    WHERE Name = 'VI_ALL_CITIES_AS_CATEGORY'
)
BEGIN

    EXEC('CREATE VIEW VI_ALL_CITIES_AS_CATEGORY AS SELECT 1 as Val')

END
GO

ALTER VIEW VI_ALL_CITIES_AS_CATEGORY
AS
SELECT 
    PERSONS.FIRST_NAME AS 'Име',
    PERSONS.LAST_NAME AS 'Фамилия',
    CITIES.CITY_NAME AS 'Град'
    FROM CITIES
       LEFT JOIN PERSONS 
          ON CITIES.ID = PERSONS.CITY_ID