SQL Server: Why can't I create a view inside of a Begin...End Block

Question

This Code:

BEGIN CREATE VIEW [dbo].[dummy] AS SELECT 1 AS Dummy END GO

Does not work, returning the error: Incorrect syntax near the keyword 'VIEW'.

Why?

Notes:

• The presence of the GO statement seems to make no difference

• The inner statement works fine outside of the code block delimiters.

• This is part of a larger query but tested in isolation just as it is presented here.

Answer 1

It's because CREATE VIEW must be the first statement in a batch as described in this MSDN reference.

Instead, you could do: e.g.

.....
    BEGIN 
        EXECUTE('CREATE VIEW [dbo].[dummy] AS SELECT 1 AS Dummy')
    END

Answer 2

You can use three wayes to create temporary view.

1-AdaTheDev answer.

2-create a temporary table then inserts the value in it e.g create Table #TableName (ID integer). See this Link

3- Using Common Table Expression [With]. See this Link