creating / updating view using stored procedure

问题:

I want to create or update a view using stored procedure like this:

CREATE PROC Proc_Get_Ready_Weapons
AS
BEGIN

  IF EXISTS(select * FROM sys.views where name = 'dbo.vwGetReadyWeapons')
  BEGIN
    EXEC ('CREATE VIEW dbo.vwGetReadyWeapons ... rest of view')
  END
  ELSE
  BEGIN
    EXEC ('CREATE OR REPLACE VIEW dbo.vwGetReadyWeapons ... rest of view')
  END

  IF @@ROWCOUNT = 0
    PRINT 'Warning: No rows were updated'  
END

But getting this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.
Warning: No rows were updated

回答1:

CREATE OR REPLACE is not valid in SQL Server (at least not yet).

Perhaps you meant:

EXEC('ALTER VIEW dbo. ...');

You also don't have a valid check. I think you meant:

IF NOT EXISTS 
  (SELECT 1 FROM sys.views WHERE [object_id] = OBJECT_ID('dbo.vwGetReadyWeapons'))