creating multiple views in the same script in SQL Server

Question

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.

Run the SQL... You won't get the error. That's intellisense saying that the VIEW doesn't exist, because at the time of it parsing the SQL is doesn't, and the VIEW is being created in a separate scope; so it has no idea it's being created. TL;DR: The SQL you've provided works fine: db<>fiddle — Thom A
– Thom A ♦, Commented Feb 21, 2022 at 14:12
if you add more after what you've shown us, you'll need another "GO" to keep your "ALTER VIEW..." in its own batch. (Or else use EXEC('...') which always executes in a separate batch). — RBarryYoung
– RBarryYoung, Commented Feb 21, 2022 at 14:16
the problem is the red underline extends to other parts of the code so eventually I will be in a situation where everything is underlined and I will get a real error and I will not be able to detect the problem. And also, I don't understand why it works and at the same time it underlines and advise of syntax errors. — Miquel
– Miquel, Commented Feb 21, 2022 at 14:16
IntelliSense reports possible errors as suggestions, not a certain runtime error. IntelliSense does not consider dynamic SQL. — Dan Guzman
– Dan Guzman, Commented Feb 21, 2022 at 14:17
@Miquel No, it's just that the Intellisense for a session doesn't update the way that you think it would, but that doesn't affect the compiler at all. If it bothers you, just create a new session and see if the lines are there. — RBarryYoung
– RBarryYoung, Commented Feb 21, 2022 at 14:18

David Browne - Microsoft · Accepted Answer · 2022-02-21 14:44:52Z

2

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

edited Feb 21, 2022 at 14:44

answered Feb 21, 2022 at 14:29

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Miquel Over a year ago

And I would be able to replicate this many times in the same script?

David Browne - Microsoft Over a year ago

Seperated by GO, yes.

Miquel Over a year ago

In which position should the "GO" be placed?

David Browne - Microsoft Over a year ago

On a line by itself separating the script into separate batches. CREATE OR ALTER VIEW must be the only statement in a batch. See updated answer.

Miquel Over a year ago

Thank you very much, very good and simple solution for all.

Collectives™ on Stack Overflow

creating multiple views in the same script in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related