chohi chohi - 4 months ago 9
SQL Question

whats the error in creating this sql server view?

I am getting an error incorrect syntax near the key word 'view'

if object_id('my_view') is null
BEGIN
create view dbo.my_view as
select * from dbo.my_table;
END

Answer

Syntax indicates that it is SQL Server. You could use dynamic sql:

IF OBJECT_ID('my_view') IS NULL
BEGIN
EXEC('CREATE VIEW dbo.my_view
      AS
      SELECT * 
      FROM dbo.my_table;');
END;

LiveDemo

The point is that CREATE VIEW:

A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns.

One way to achieve it is to use Dynamic-SQL to create new "context", where DDL is the first statment in batch.

Also I strongly recommend to specify all columns explicitly. Using SELECT * is common anti-pattern.

Comments