为什么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.
我的问题:
- 为什么不支持此功能?从sproc运行此代码并将其作为独立语句运行有什么区别?
- 为什么通过
exec
输入文字来解决此问题SQL字符串工作?我对exec
语句的理解是,它只是直接执行SQL,是不正确的吗? - (不乐观)是吗?还有什么更好的方法可以从存储过程中更改视图?
- Why is this not supported? What's the difference between running this from a sproc and running it as a standalone statement?
- Why does the workaround through
exec
ing the literal SQL string work? My understanding of theexec
statement is that it just executes the SQL in-line, is that incorrect? - (Not optimistic) Is there any better way to make a change to a view from within a stored procedure?
推荐答案
我认为答案是:
- MS希望防止DDL在过程中运行。
- exec语句内的代码 未被视为过程的一部分-因此它不受与过程相同的限制。
- 否。
- MS want to prevent DDL being run from within procedures.
- 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.
- 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屋!