SQL 从函数执行存储过程

Execute Stored Procedure from a Function

提问by Smur

I know this has been asked to death, and I know why SQL Server doesn't let you do it.

我知道这已经被要求死了，我知道为什么 SQL Server 不允许你这样做。

But is there any workaround for this, other than using Extended Stored Procedures?

但是除了使用扩展存储过程之外，还有其他解决方法吗？

And please don't tell me to convert my function into a procedure...

并且请不要告诉我将我的函数转换为程序...

So what I'm really asking is: Is there ANY way to run a Stored Procedure from within a Function?

所以我真正要问的是：有没有办法从函数内运行存储过程？

EDIT:

编辑：

Point proven: there is a way around it, but it's so WRONGI wouldn't do it. I'm gonna change it to a Stored Procedure and execute it elsewhere.

事实证明：有一种方法可以解决它，但这是错误的，我不会这样做。我要将其更改为存储过程并在其他地方执行。

采纳答案by Tom Chantler

EDIT: I haven't tried this, so I can't vouch for it! And you already know you shouldn't be doing this, so please don't do it. BUT...

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

Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

试试看这里：http: //sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

The key bit is this bit which I have attempted to tweak for your purposes:

关键是这一点，我试图为您的目的进行调整：

DECLARE @SQL varchar(500)

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

EXEC master..xp_cmdshell @SQL

回答by MatBailie

Functions are not allowed to have side-effects such as altering table contents.

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

Stored Procedures are.

存储过程是。

If a function called a stored procedure, the function would become able to have side-effects.

如果一个函数调用了一个存储过程，这个函数就会产生副作用。

So, sorry, but no, you can't call a stored procedure from a function.

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

回答by Solomon Rutzky

Another option, in addition to using OPENQUERY and xp_cmdshell, is to use SQLCLR (SQL Server's "CLR Integration" feature). Not only is the SQLCLR option more secure than those other two methods, but there is also the potential benefit of being able to call the stored procedure in the current sessionsuch that it would have access to any session-based objects or settings, such as:

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

• temporary tables
• temporary stored procedures
• CONTEXT_INFO

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

This can be achieved by using "context connection = true;" as the ConnectionString. Just keep in mind that all other restrictions placed on T-SQL User-Defined Functions will be enforced (i.e. cannot have any side-effects).

这可以通过使用“context connection = true;”来实现。作为连接字符串。请记住，将强制执行对 T-SQL 用户定义函数的所有其他限制（即不会产生任何副作用）。

If you use a regular connection (i.e. not using the context connection), then it will operate as an independent call, just like it does when using the OPENQUERY and xp_cmdshell methods.

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

HOWEVER, please keep in mind that if you will be using a function that calls a stored procedure (regardless of which of the 3 noted methods you use) in a statement that affects more than 1 row, then the behavior cannot be expected to run once per row. As @MartinSmith mentioned in a comment on @MatBailie's answer, the Query Optimizer does not guarantee either the timing or number of executions of functions. But if you are using it in a SET @Variable = function();statement or SELECT * FROM function();query, then it should be ok.

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

An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote):

使用 .NET / C# SQLCLR 用户定义函数执行存储过程的示例如下文（我写的）所示：

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

通往 SQLCLR 级别 2 的阶梯：示例存储过程和函数

回答by C Rudolph

I have figured out a solution to this problem. We can build a Function or View with "rendered" sql in a stored procedure that can then be executed as normal.

我已经找到了解决这个问题的方法。我们可以在存储过程中使用“渲染”的 sql 构建一个函数或视图，然后可以正常执行。

1.Create another sproc

1.创建另一个sproc

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

2.Build the dynamic sql that you want to execute in your function (Example: you could use a loop and union, you could read in another sproc, use if statements and parameters for conditional sql, etc.)

2.构建你想在你的函数中执行的动态sql（例如：你可以使用循环和联合，你可以读入另一个sproc，使用if语句和条件sql的参数等）

SET @innerSql = 'your sql'

3.Wrap the @innerSql in a create function statement and define any external parameters that you have used in the @innerSql so they can be passed into the generated function.

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

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


EXEC(@outerSql)

This is just pseudocode but the solution solves many problems such as linked server limitations, parameters, dynamic sql in function, dynamic server/database/table name, loops, etc.

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

You will need to tweak it to your needs, (Example: changing the return in the function)

您需要根据需要对其进行调整，（例如：更改函数中的返回值）

回答by Vadzim

Here is another possible workaround:

这是另一种可能的解决方法：

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()

It's not so scary as xp_cmdshellbut also has too many implicationsfor practical use.

它没有那么可怕，xp_cmdshell但对实际使用也有太多影响。