c# - 'CREATE VIEW' 必须是查询批处理中的第一个语句

Question

这是我在 C# 中的脚本:

exec sp_executesql N'
IF OBJECT_ID(N''RealEstate.vwContract'', N''V'') IS NOT NULL
  DROP VIEW RealEstate.vwContract

CREATE VIEW RealEstate.vwContract
AS
  SELECT RealEstate.Contract.ID .... (Rest of Statement omitted for brevity)

错误显示:

Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.

请帮帮我。

Answer 1

信息不言自明； create view 必须是第一条语句 - 但您可以作弊。我的创建脚本(如果我需要从 ADO.NET 运行它们，那么不用 GO)看起来很像:

if not exists(select 1 from sys.tables where name='SomeTable')
begin
    exec('create table SomeTable ....blah not shown');
    -- more to add indexing etc
end
if not exists(select 1 from sys.tables where name='SomeOtherTable')
begin
    exec('create table SomeOtherTable ....blah not shown');
    -- more to add indexing etc
end

您可以使用 sys.views 做同样的事情。也许，未经测试:

if exists (select 1 from sys.views where name = 'MyView')
    exec ('drop view MyView');
exec ('create view MyView ...blah not shown...');