How to call a Stored procedure form a user defined function In SQL 2000
如何从用户定义的函数调用存储过程在SQL 2000中
2 个解决方案
#1
officially you can't.
官方你不能。
but you can try this trick:
但你可以试试这个伎俩:
-- add 'loopback' linkedserver
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
select * from openquery(loopback, 'exec yourSproc')
go
#2
According to this article, one of the limitations is that you can't call an SP from a UDF.
根据这篇文章,其中一个限制是您无法从UDF调用SP。
One of the features of a UDF is that they're deterministic - calling them repeatedly with the same input values will result in the same output values (also assuming the underlying data isn't changing). If you call other objects from a UDF, SQL Server can't guarantee that this will remain true - that the UDF will remain deterministic. For example, if you call a SP from your UDF, even if the SP is currently deterministic (doesn't contain any non-deterministic functions, like GETDATE), there's no guarantee that won't change.
UDF的一个特性是它们是确定性的 - 用相同的输入值重复调用它们将产生相同的输出值(也假设底层数据没有变化)。如果从UDF调用其他对象,则SQL Server无法保证这将保持为真 - UDF将保持确定性。例如,如果从UDF调用SP,即使SP当前是确定的(不包含任何非确定性函数,如GETDATE),也不能保证不会更改。
For an explanation of what it means to be (non-)deterministic, check out wiki or MSDN
有关(非)确定性的含义的解释,请查看wiki或MSDN
#1
officially you can't.
官方你不能。
but you can try this trick:
但你可以试试这个伎俩:
-- add 'loopback' linkedserver
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
select * from openquery(loopback, 'exec yourSproc')
go
#2
According to this article, one of the limitations is that you can't call an SP from a UDF.
根据这篇文章,其中一个限制是您无法从UDF调用SP。
One of the features of a UDF is that they're deterministic - calling them repeatedly with the same input values will result in the same output values (also assuming the underlying data isn't changing). If you call other objects from a UDF, SQL Server can't guarantee that this will remain true - that the UDF will remain deterministic. For example, if you call a SP from your UDF, even if the SP is currently deterministic (doesn't contain any non-deterministic functions, like GETDATE), there's no guarantee that won't change.
UDF的一个特性是它们是确定性的 - 用相同的输入值重复调用它们将产生相同的输出值(也假设底层数据没有变化)。如果从UDF调用其他对象,则SQL Server无法保证这将保持为真 - UDF将保持确定性。例如,如果从UDF调用SP,即使SP当前是确定的(不包含任何非确定性函数,如GETDATE),也不能保证不会更改。
For an explanation of what it means to be (non-)deterministic, check out wiki or MSDN
有关(非)确定性的含义的解释,请查看wiki或MSDN