从函数中执行存储过程

Question

我知道这个问题已经问了很多次，也知道为什么SQL Server不允许这样做。

但有没有任何解决方法，除了使用扩展存储过程之外？

请不要告诉我将我的函数转换为存储过程……

所以我真正想问的是：有没有任何方法可以从函数内运行存储过程？

编辑：

证明了一点：有一种方法可以绕过它，但它非常错误，我不会这样做。我将把它更改为存储过程，并在其他地方执行。

- Smur

---

+1 对于这个问题的一般建议是不要这样做，因为这可能会对其他考虑这样做的人起到警示作用。 - Tom Chantler

@Smur：在我的下面的答案中，我描述了另一种方法，虽然在多行上下文中执行时仍有潜在问题，但比使用xp_cmdshell调用osql（实际上应该是SQLCMD）要好得多。 - Solomon Rutzky

Answer 1

编辑：我没有尝试过这个方法，所以不能保证效果！而且你已经知道不应该这样做，所以请不要这样做。但是...

试一下在这里找：http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

关键的部分就是这段代码，为了你的目的，我已经尝试进行了修改：

DECLARE @SQL varchar(500)

SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec dbName..sprocName "'

EXEC master..xp_cmdshell @SQL

- Tom Chantler

---

当我读到这句话时，我内心有一部分口水流了出来。但更大的一部分则惊恐地尖叫着跑来跑去，像只鸡一样咯咯叫……说真的，如果你希望拥有一个可用、友好的环境，那就不要找出允许函数具有副作用的黑科技，除非你只需要维持明天这么长的时间。 - MatBailie

如果 OP 这样做，你想让我失业吗？那太严厉了 ;( - MatBailie

我？冷静？我很冷静！你看，我几乎一点都不发抖……你说我像小鸡一样叽叽喳喳？这不是我干的！我告诉你，不是我！别那样盯着我看！ - MatBailie

笑。我应该保留它还是删除它？ - Smur

人们会嘲笑这些评论。保留它。 - Smur

Answer 2

函数不允许具有副作用，如改变表内容。

储存过程是允许的。

如果函数调用了储存过程，则该函数将能够具有副作用。

所以，很抱歉，您不能从函数中调用存储过程。

- MatBailie

---

你的回答缺乏解释。主要问题在于为什么不允许函数，你只是将同一句话重新表述了三种不同形式。如果是关于引用透明度，那就说明白点。如果是关于效率问题，也请说明白，不要让人感到困惑。 - Luka Ramishvili

@LukaRamishvili - 我们有些许不同意见。"吸烟会导致癌症因为吸烟是致癌的" 确实是循环论证。但是 "函数不能调用存储过程因为函数不允许引起副作用" 并非循环论证。这可能会有一个额外的问题：“为什么函数不能引起副作用？” 但这确实只是一个额外的问题。为什么可以问到任何层次？最终涉及优化器的设计和实现的详细信息。在我看来，这不是这样的论坛。 - MatBailie

我们不要争论这个问题，你是对的。OP没有问为什么，而你的回答很直接——“不行”。但这听起来像是一种信条，即[T-SQL]函数不允许有副作用。重要的是要澄清限制它的原因，首先为什么函数不允许有副作用，并且这也可以解释存储过程为什么不允许有副作用的原因。 - Luka Ramishvili

@LukaRamishvili 可能的原因是执行次数和执行时间取决于执行计划。同一查询可能会有不同数量的函数执行，这取决于它们是在过滤器之前还是之后进行评估。即使对于相同的计划，它在冷缓存与热缓存下也可能会有所不同。[示例]（http://sqlblog.com/blogs/paul_white/archive/2013/08/31/sql-server-internals-nested-loops-prefetching.aspx） - Martin Smith

这个答案在其他回答中展示的三种方法中是明显错误的。是否应该这样做是另一回事，这取决于上下文（例如函数只运行一次，如 SET @Variable = function，或者可能由于作为查询的一部分而运行多次，即使它只返回单个行）。此外，“如果一个函数调用了存储过程，那么函数就能够产生副作用”也不是真的，因为 SQL Server 可以防止那些被禁止的操作，就像从 SQLCLR 函数调用存储过程时所做的那样。 - Solomon Rutzky

Answer 3

另一个选项，除了使用 OPENQUERY 和 xp_cmdshell 之外，是使用 SQLCLR（SQL Server 的“CLR 集成”功能）。 SQLCLR 选项不仅比其他两种方法更安全，而且还有潜在的好处，即能够调用存储过程“在当前会话中”，使其可以访问任何基于会话的对象或设置，例如：

• 临时表
• 临时存储过程
• CONTEXT_INFO

这可以通过使用“context connection = true;”作为 ConnectionString 来实现。只需记住，所有其他对 T-SQL 用户定义函数施加的限制都将被执行（即不能具有任何副作用）。

如果您使用常规连接（即不使用上下文连接），则它将像使用 OPENQUERY 和 xp_cmdshell 方法时一样作为独立调用运行。

然而，请记住，如果您将使用调用存储过程的函数（无论您使用哪种3种方法），在影响超过1行的语句中，则不能期望其行为每行运行一次。正如@MartinSmith在对@MatBailie答案的评论中提到的那样，查询优化器不保证函数执行的时间或次数。但是，如果您在SET @Variable = function();语句或SELECT * FROM function();查询中使用它，则应该没问题。

以下文章展示了使用.NET/C# SQLCLR用户定义函数执行存储过程的示例（由我撰写）：Stairway to SQLCLR Level 2: Sample Stored Procedure and Function。

- Solomon Rutzky

Answer 4

我已经想出了这个问题的解决方案。我们可以在存储过程中构建具有“渲染”SQL的函数或视图，然后像正常情况下一样执行它。

1.创建另一个存储过程

CREATE PROCEDURE [dbo].[usp_FunctionBuilder]
DECLARE @outerSql VARCHAR(MAX)
DECLARE @innerSql VARCHAR(MAX)

2. 在您的函数中构建要执行的动态 SQL（例如：您可以使用循环和联合，读取另一个存储过程，使用if语句和参数进行条件SQL等）

SET @innerSql = 'your sql'

3. 将 @innerSql 包装在一个 create function 语句中，并定义您在 @innerSql 中使用的任何外部参数，以便可以将它们传递到生成的函数中。

SET @outerSql = 'CREATE FUNCTION [dbo].[fn_GeneratedFunction] ( @Param varchar(10))
RETURNS TABLE
AS
RETURN
' + @innerSql;


EXEC(@outerSql)

这只是伪代码，但该解决方案可以解决许多问题，例如链接服务器限制、参数、函数中的动态SQL、动态服务器/数据库/表名、循环等。

您需要根据自己的需求进行调整（例如更改函数中的返回值）。

- C Rudolph

Answer 5

这里还有另一种可能的解决方案：

if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback', @srvproduct = N'', @provider = N'SQLOLEDB', @datasrc = @@servername
go

create function testit()
    returns int
as
begin
    declare @res int;
    select @res=count(*) from openquery(loopback, 'exec sp_who');
    return @res
end
go

select dbo.testit()

它并不像xp_cmdshell那样可怕，但在实际使用中也有太多的含义。

- Vadzim

---

你如何向这个函数添加一个参数？ - Siyabonga Dube

@SiyabongaDube，我的回答中的链接包含了一个例子来解决这个问题。 摘录：“OPENQUERY 的第二个参数是在远程服务器上运行的查询，你可能希望能够在此处使用变量，但实际上不行。查询字符串必须是一个常量，因为 SQL Server 需要在编译时确定结果集的形状。这意味着，一旦你的查询具有参数值，你就需要使用动态 SQL。” - Vadzim