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

Question

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
  ...

What version of SQL? There are increasingly more usable (imo) approaches as you go up in version. — Jacob H, May 09 '18 at 16:11
Seems to be a duplicate of: https://stackoverflow.com/questions/18534919/how-to-make-create-or-replace-view-work-in-sql-server — xQbert, May 09 '18 at 16:13
@xQbert SQL Server 2016 has `CREATE OR ALTER` and `DROP IF EXISTS`. This only appears in the fourth answer to the linked question. This isn't the best duplicate — Panagiotis Kanavos, May 09 '18 at 16:17
@PanagiotisKanavos While true, neither of those completely solves the use case if the OP only wants to create the view if it doesn't already exist, but does **not** want to change it if it already exists. In that case I think they'll need `IF NOT EXISTS () ... dynamic SQL`. — Aaron Bertrand, May 09 '18 at 16:34

score 2 · Answer 1 · answered May 09 '18 at 16:14

2

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.

answered May 09 '18 at 16:14

dfundako

8,022
3
18
34

Thanks @dfundako, I definitely do not want to ALTER if it exists. I just simply do not want the statement to do anything if it already exists. – Mike Marks May 09 '18 at 16:29
@MikeMarks Well if you pass the same code every time, it won't really alter anything. But it is still running an operation against the object. – dfundako May 09 '18 at 16:30

score 0 · Answer 2 · answered May 09 '18 at 16:53

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

Be careful splattering that NOLOCK hint all over the place. It has lots of fun and impossible to recreate anomalies like missing and/or duplicate rows. It is almost always a bad decision in a view unless you really don't care about the accuracy of your data. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, May 09 '18 at 18:20

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

2 Answers2