CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
Mike Marks
Mike Marks

Reputation: 10139

How to perform an IF/ELSE check when creating a view

I'm writing a script to create a view, only IF the view does not already exist. If the view does already exist, I don't want to alter it or drop and re-create it. The syntax below is obviously not complete, and generates an error because CREATE VIEW needs to be in its own batch - but what is the proper way to construct my use case?

IF OBJECT_ID('dbo.view_name') IS NULL 
BEGIN    
  CREATE VIEW [dbo].[view_name]
  AS
      SELECT ...;
END
ELSE
  ...

Upvotes: 0

Views: 77

Answers (2)

Mike Marks
Mike Marks

Reputation: 10139

I changed the SQL to be the following. To avoid the "CREATE VIEW must be in its own batch" error, I wrapped the "CREATE VIEW" inside an exec('') statement. This works!

USE [XXXXXXXXX]
    GO

    /****** Object:  View [CXXXXXXX].[_MobileMessageTracking_v2]    Script Date: 5/4/2018 3:19:04 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    IF Object_id('CXXXXXXX._MobileMessageTracking_v2') IS NULL BEGIN

    exec('CREATE VIEW [CXXXXXXX].[_MobileMessageTracking_v2]
            AS
                    SELECT  
                        /* fields here */
                    FROM CXXXXXXXX._MobileMessageTracking AS M WITH (NOLOCK)
                    WHERE M.MID = XXXXXXX
                                    AND M.CreateDateTime >= DATEADD(mm,-6, GETDATE())

                    UNION
                    select
                        /* fields here */')
    END

    GO

Upvotes: 0

dfundako
dfundako

Reputation: 8324

SQL Server 2016 has CREATE OR ALTER.

CREATE OR ALTER VIEW vw_your_view 
AS 
SELECT 1 FROM your_Table
GO

This will blow up if you move it to an environment below SQL Server 2016. If that is the case, go with what you have and check for an obj ID.

Upvotes: 2

Related Questions