从函数执行存储过程

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 WRONG I wouldn't do it. I'm gonna change it to a Stored Procedure and execute it elsewhere.

点证明:有一个方法可以绕过它，但它是如此的错误，我不会这么做。我将把它改为一个存储过程并在其他地方执行。

4 个解决方案

#1

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

#2

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.

不好意思，你不能从函数中调用存储过程。

#3

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 session such 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).

这可以通过使用“上下文连接= true;”作为ConnectionString实现。只要记住，对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.

但是，请记住，如果您将在一个影响超过一行的语句中使用一个调用存储过程的函数(不管您使用的是哪一个注意到的方法)，那么不能期望该行为每一行运行一次。正如@MatBailie的回答中提到的@MartinSmith，查询优化器不能保证函数的执行时间或执行次数。但是如果您在SET @Variable = function()中使用它;语句或从函数()中选择*;查询，那么它应该是ok的。

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的楼梯:示例存储过程和函数

#4

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_cmdshell but also has too many implications for practical use.

它不像xp_cmdshell那么可怕，但是对于实际应用来说也有太多的含义。

#1