为什么我们不能在 SQL Server 中的函数内部执行存储过程 [英] Why can we not execute a stored procedure inside a function in SQL Server

问题描述

为什么在相反的情况下我们不能在函数内部执行存储过程?

Why can we not execute a stored procedure inside a function when the opposite is possible?

推荐答案

不能在函数内部执行存储过程，因为函数不允许修改数据库状态，而存储过程是修改数据库状态.

You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

这是根据定义(参见 创建功能 - 限制和限制).

This is by definition (see CREATE FUNCTION - Limitations and Restrictions).

用户定义的函数不能用于执行修改数据库状态的操作.

User-defined functions cannot be used to perform actions that modify the database state.

存储过程可能会修改数据库状态，也可能不会.但是 SQL Server 编译器不必分析存储过程以了解它是否修改了数据库状态.因此，不允许在函数内部执行存储过程.

A stored procedure might modify database state, or it might not. But the SQL Server compiler shouldn't have to analyze the stored procedure to know whether or not it modifies database state. Therefore, it is not allowed to execute a stored procedure from within a function.

函数的存在只是为了计算某些东西，一个值或一个表结果，仅此而已.例如，可以在 SELECT 查询中调用这些函数，例如

Functions exist to simply calculate something, a value or a table result, nothing more than that. These functions can be called within a SELECT query for instance, e.g.

SELECT calculate_something(a) FROM some_table;

现在考虑如果允许函数 calculate_something 执行一个存储过程，该过程将删除 some_table 中的所有行，会发生什么情况.您的意图是使用 some_table.a 列的值计算某些内容，但最终...删除了 some_table 中的所有行.这显然不是您想要发生的事情.

Now consider what would happen if the function calculate_something were allowed to execute a stored procedure which would delete all rows in some_table. Your intention is to calculate something using the value of the some_table.a columns, but you end up... deleting all rows in some_table. That is clearly not something you want to happen.

这篇关于为什么我们不能在 SQL Server 中的函数内部执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！