为什么SQL Server无法在存储过程中更改视图？ [英] Why can't SQL Server alter a view in a stored procedure?

问题描述

我正在使用MS SQL Server，我想通过执行类似将VIEWNAME更改为（[some sql]）之类的东西来更改存储过程中的视图。

I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])".

由Google抛出的一些页面断言，它不直接受支持（也没有相关的alter-table语句），但是也有一些示例，说明了如何使用如下结构来解决它： / p>

A few pages thrown up by google assert that this is not supported directly (and neither are related alter-table statements), but there are also examples of how to work around it using constructions like this:

declare @sql varchar(max)
select @sql = 'alter view VIEWNAME as ([some sql])'
exec(@sql)

以文字字符串的形式编写代码有点味道，即使对于SQL也是如此。

Writing code as literal strings smells a bit, even for SQL.

我的问题：

1. 为什么不支持此功能？从sproc运行此代码并将其作为独立语句运行有什么区别？


2. 为什么通过 exec 输入文字来解决此问题SQL字符串工作？我对 exec 语句的理解是，它只是直接执行SQL，是不正确的吗？


3. （不乐观）是吗？还有什么更好的方法可以从存储过程中更改视图？

1. Why is this not supported? What's the difference between running this from a sproc and running it as a standalone statement?
2. Why does the workaround through execing the literal SQL string work? My understanding of the exec statement is that it just executes the SQL in-line, is that incorrect?
3. (Not optimistic) Is there any better way to make a change to a view from within a stored procedure?

推荐答案

我认为答案是：

1. MS希望防止DDL在过程中运行。


2. exec语句内的代码 未被视为过程的一部分-因此它不受与过程相同的限制。


3. 否。

1. MS want to prevent DDL being run from within procedures.
2. The code within the exec statement is not treated as part of the procedure - so it is not subject to the same restrictions as the procedure.
3. No.

另一种方法可能是拥有一个单独的表（称为swing_table之类的表），记录1或0，以指示视图是否应查询生产或其他（备份？）表-类似于：

An alternative approach might be to have a separate table (called something like swing_table) with either 1 or 0 records to indicate whether the view should query the production or other (backup?) table respectively - something like:

create view viewname as
select {field list}
from production_table
cross join swing_table
union all
select {field list}
from backup_table
where (select count(*) from swing_table) = 0

-当您想对表进行摆动时，在过程中执行TRUNCATE swing_table-由于TRUNCATE不是事务性命令，因此应立即执行。

- then TRUNCATE swing_table within the procedure when you want to, erm, swing the table - since TRUNCATE is not a transactional command, it should execute immediately.

这篇关于为什么SQL Server无法在存储过程中更改视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！