creating multiple views in the same script in SQL Server

February 21, 2022

I have a table that is the result of an INNER JOIN and then to that table I have to a apply a bunch of queries, so at the end the whole code in SQL is really big and in the future (and that’s the main problem) I will have problems to understand what did I do.

So for this reason I am considering the possibility of creating views, so in each step I have a view created and I know what does each one of the queries.

So with that goal I started to use IF ELSE statements to create views if they dont’ exist etc. but I’m stumbling with a lot of errors and problems.

First of all, this is the way I’m creating a view with the IF statement:

-- This portion of code is common in all the views
IF NOT EXISTS
(
    SELECT 1
    FROM sys.views
    WHERE Name = 'NAME_OF_THE_VIEW'
)
BEGIN

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

END

GO
ALTER VIEW NAME_OF_THE_VIEW
AS

-- Here I put the query of the view

SELECT *
FROM table_1

When I execute this code it works, but the SQL Server Management Studio underlines "NAME_OF_THE_VIEW" in the ALTER VIEW statement and when I hover the mouse it says: Invalid object name ‘NAME_OF_THE_VIEW’. I don’t understand why if there’s a supposed error it still works.

The other problem is that when I introduce more code like the code above in order to create other views in the same script, the whole ALTER VIEW statement is underlined and when I hover this message appears; Incorrect syntax: ‘ALTER VIEW’ must be the only statement in the batch.

So the question is: hoy can I put everything in the same script, where I can create views to avoid doing a lot of subqueries, and without getting this errors? The SQL-Server version is 15.

>Solution :

So the question is: hoy can I put everything in the same script, where I can create views to avoid doing a lot of subqueries, and without getting this errors?

There’s no need to check for existence of the view. Just CREATE OR ALTER VIEW

CREATE OR ALTER VIEW NAME_OF_THE_VIEW
AS

-- Here I put the query of the view

SELECT *
FROM table_1

GO

CREATE OR ALTER VIEW NAME_OF_THE_OTHER_VIEW
AS

-- Here I put the query of the view

SELECT *
FROM table_1