Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why can't I create a view inside of a BEGIN ... END block

Tags:

sql

sql-server

This code does not work, returning the error:

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

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.

like image 628
Adam Tolley Avatar asked Jun 28 '10 15:06

Adam Tolley


1 Answers

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
like image 55
AdaTheDev Avatar answered Sep 21 '22 19:09

AdaTheDev