what this Error message "Incorrect syntax near the keyword 'VIEW'."

Question

CREATE PROCEDURE ViewCreater
AS
   BEGIN
   CREATE VIEW [V97] AS    
   SELECT title,type1,Tedat,DATEADD(DAY,-2,DATEADD(YEAR,-1,Tarikh))AS NewDate97, Descrip FROM Test1;  
Go;

Does this answer your question? [Creating a View using stored procedure](https://stackoverflow.com/questions/7712702/creating-a-view-using-stored-procedure) — Tomislav Baljint, Feb 10 '20 at 08:06
A stored procedure is meant to be called repeatedly, a view is meant to be *created* once and *queried* repeatedly. So creating a view in an SP sounds like a mismatch ... — Hans Keﬆing, Feb 10 '20 at 08:50

score 0 · Answer 1 · answered Feb 10 '20 at 08:09

A stored procedure cannot be created with the create View command in it

More information available in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/af8b0c2d-8117-42e3-a701-05035644f73d/is-it-possible-to-create-a-view-within-a-stored-procedure?forum=sqlgetstarted

score 0 · Accepted Answer · answered Feb 10 '20 at 08:12

You can use the following code.

CREATE PROCEDURE ViewCreater
AS
BEGIN
    IF EXISTS (
           SELECT 1
           FROM   sysobjects
           WHERE  NAME          = 'Test1'
                  AND xtype     = 'V'
       )
    BEGIN
        DROP VIEW dbo.vw00Menu;
    END

    EXEC sp_executesql 
         N'CREATE VIEW [V97] AS    
           SELECT title,type1,Tedat,
           DATEADD(DAY,-2,DATEADD(YEAR,-1,Tarikh))AS NewDate97, Descrip FROM Test1'
END

what this Error message "Incorrect syntax near the keyword 'VIEW'."

2 Answers2