如何从用户定义的函数调用存储过程在SQL Server 2000中

How to call a stored procedure from a user defined function in SQL Server 2000

如何从SQL Server 2000中的用户定义函数调用存储过程

3 个解决方案

#1

Either you need to modify your stored procedure to be a user defined function or the other way around.

要么您需要将存储过程修改为用户定义的函数,要么相反。

One crude way to achieve what you are looking for is to have your exec statement in a batch script and call that batch script from your function. Something like this:

实现所需内容的一种粗略方法是在批处理脚本中使用exec语句,并从函数中调用该批处理脚本。像这样的东西:

create function <functionName>
exec master.sys.xp_cmpshell 'C:\storedProc.bat'
....
....

return @return
end

#2

You can't call regular stored procs from functions - only other functions or some extended stored procedures. See here for the BOL article (from SQL 2005). Attempting to call a standard stored proc from a UDF will result in the following error...

您不能从函数调用常规存储过程 - 只能调用其他函数或某些扩展存储过程。请参阅此处获取BOL文章(来自SQL 2005)。尝试从UDF调用标准存储过程将导致以下错误...

Msg 557, Level 16, State 2, Line 1 Only functions and some extended stored procedures can be executed from within a function.

消息557,级别16,状态2,行1仅可以在函数内执行函数和一些扩展存储过程。

#3

I recently had a similar issue. In fact the error message is not properly formatted since sp_executesql is an extended stored procedure as you can check by the following script: select objectproperty(object_id('sp_executesql'),'IsExtendedProc')

我最近有类似的问题。实际上错误消息没有正确格式化,因为sp_executesql是一个扩展存储过程,您可以通过以下脚本进行检查:select objectproperty(object_id('sp_executesql'),'IsExtendedProc')

returns 1

Since we can’t use sp_executesql even it’s an XP, I had to find another workaround by using sp_OAMethod. My scenario was how to find the number of rows dynamically in a table according to some criteria (null values in my scenario). Using sp_OAMethod I built the following function:

由于我们不能使用sp_executesql,即使它是XP,我也必须通过使用sp_OAMethod找到另一种解决方法。我的场景是如何根据某些条件在表中动态查找行数(我的方案中为空值)。使用sp_OAMethod我构建了以下函数:

IF object_id(N'dbo.fc_ContaRegistros_x_Criterio') is not null DROP FUNCTION [dbo].[fc_ContaRegistros_x_Criterio]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.fc_ContaRegistros_x_Criterio (
    @str_TBName VARCHAR(100), 
    @str_Criter VARCHAR(500)
)
RETURNS BIGINT
AS
BEGIN
-- Objetivo   : Contar numero de registros de uma determinada tabela de acordo com o critério passado 
-- Criação    : Josué Monteiro Viana - 09/07/09
/*
Exemplo: 
    DECLARE @count INT
    SET @count = dbo.fc_ContaRegistros_x_Criterio('master.dbo.sysobjects', '') 
    PRINT @count
    SET @count = dbo.fc_ContaRegistros_x_Criterio('crk.dbo.acao', 'where cod_acao is null') 
    PRINT @count
*/
    DECLARE 
        @int_objSQL INT,
        @int_erros INT,
        @int_objSelectCountResult INT,
        @bint_SelectCount BIGINT,
        @sql NVARCHAR(2000)

    EXEC @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
    EXEC @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
    EXEC @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
    --SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' WHERE ' + @str_Criter 
    SET @sql = 'SELECT count(*) FROM ' + @str_TBName + ' ' + @str_Criter 
    SET @sql = 'ExecuteWithResults("' + @sql + '")'
    EXEC @int_erros = sp_OAMethod @int_objSQL, @sql, @int_objSelectCountResult OUTPUT
    EXEC @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
    EXEC @int_erros = sp_OADestroy @int_objSQL
    -- debug info: not valid inside a fc
    --if @int_erros <> 0 EXEC sp_OAGetErrorInfo @int_objSQL else print 'ok'
    if @int_erros <> 0 SET @bint_SelectCount = @int_erros
    RETURN @bint_SelectCount
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I know your case is a little different, but I’m sure you can use this udf as a guideline to help you.

我知道你的情况有点不同,但我相信你可以用这个udf作为指导来帮助你。

Best wishes, Josue Monteiro Viana

最好的祝福,Josue Monteiro Viana

#1