Francho Francho - 9 days ago 5
SQL Question

CREATE VIEW with existence check

I'm trying to do this:

IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[viewName]')
AND type in (N'U')
)

CREATE VIEW [dbo].[viewName] AS
-- Such and such
GO


This will throw the "'CREATE VIEW' must be the only statement in the batch" error.

I've tried suggestions from here (surround with BEGIN/END) and here (use GO statements, use dynamic SQL) and none alleviate the error.

Answer

Try this logic, yours is a little off;

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id= object_id(N'[dbo].[ViewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)

I'll be honest, I always drop and recreate to ensure you're running the latest version of the view, something like this;

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id= object_id(N'[dbo].[ViewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)
BEGIN
    DROP VIEW [dbo].ViewName
END
GO

CREATE VIEW ViewName
AS
    SELECT 

Yeah, so checking the comments you'll want to use dynamic SQL;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[viewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)
BEGIN
DECLARE @sql nvarchar(MAX)
--SELECT 'Do this mofo'
SET @sql = N'CREATE VIEW [dbo].[viewName] 
             AS
             SELECT COUNT(1) FieldName FROM sys.objects'

EXEC sp_executesql @sql

END
GO