为什么 SQL Server 不能更改存储过程中的视图？

发布于 2024-09-19 13:56:48 字数 504 浏览 12 评论 0原文

我正在使用 MS SQL Server，并且我想通过执行类似“alter view VIEWNAME as ([some sql])”之类的操作来更改存储过程中的视图。

谷歌抛出的一些页面断言这不被直接支持（也不是相关的 alter-table 语句），但也有如何使用这样的结构来解决它的示例：

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

将代码编写为文字字符串有点难闻，即使对于 SQL。

我的问题：

为什么不支持此功能？从存储过程运行它和作为独立语句运行它有什么区别？
为什么通过exec处理文字 SQL 字符串的解决方法有效？我对 exec 语句的理解是它只是内联执行 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])".

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)

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

My questions:

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 execing the literal SQL string work? My understanding of the exec 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

恰似旧人归 2024-09-26 13:57:52

我认为答案是：

MS 希望阻止 DDL 在程序内运行。
exec 语句内的代码不被视为过程的一部分 - 因此它不受与过程相同的限制。
否。

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

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 不是事务命令，所以它应该立即执行。

I think the answers are:

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.

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

- 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.

回复收藏 0 原文

~没有更多了~