0

I'm trying to script a pretty straight-forward indexed view but SQL is simply refusing my efforts. I've tried researching the issue but haven't had any luck, and unfortunately I'm not exactly a SQL expert so I'm not sure if there's something simple that I'm missing. The template for this script was handed to me by a DBA, but he doesn't know any more than I do. Here's the top end of the script where the first error appears:

--Set the options to support indexed views.  
SET NUMERIC_ROUNDABORT OFF;  
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,  
    QUOTED_IDENTIFIER, ANSI_NULLS ON;  
GO  
--Create view with schemabinding.  
IF OBJECT_ID ('[dbo].[APIMenus]', 'view') IS NOT NULL  
    DROP VIEW [dbo].[APIMenus] ;  
GO  
BEGIN
    CREATE VIEW [dbo].[APIMenus]  
    WITH SCHEMABINDING  
    AS  
        SELECT  
            [t0].[GroupName] AS [defaultValue], 
            [t1].[GroupName] AS [transValue],.....

The error is "CREATE VIEW must be the only statement in the batch" but from what I understand, wrapping it in the BEGIN...END with proper GO statements before and after should have solved the problem, yet it persists. Can anyone spot what I'm doing wrong?

thanby
  • 323
  • 1
  • 6
  • 22

2 Answers2

1

The error is accurate. Wrapping a CREATE VIEW in BEGIN/END does not work to avoid this message. If you remove the BEGIN/END entirely, so the batch starts with CREATE VIEW and ends with another GO after the view is defined, then the error will be gone, and you can continue to use this DROP before CREATE structure.

A slightly different way to handle this problem of not creating a view that already exists, is to invoke dynamic SQL:

if OBJECT_ID ('[dbo].[cars]', 'view') IS NOT NULL 
    exec ('create view cars as select * from vans where type=1')
go

Sometimes I have seen this:

if OBJECT_ID ('[dbo].[cars]', 'view') IS NOT NULL 
    exec ('create view cars as select 1')
go
alter view cars as
    select * from vans where type==1
GO

This latter technique has the advantage that your complex SQL is not embedded in a string, and you can format it nicely.

Ross Presser
  • 6,027
  • 1
  • 34
  • 66
0

Check DROP VIEW IF EXISTS and CREATE OR ALTER VIEW, both supported in SQL Server 2016. Ref: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

Desinderlase
  • 31
  • 3