Как вызвать хранимую процедуру из пользовательской функции в SQL Server 2000
Как вызвать хранимую процедуру из пользовательской функции в SQL Server 2000
Ответы:
Либо вам нужно изменить хранимую процедуру, чтобы она была определяемой пользователем функцией, либо наоборот.
Один грубый способ добиться того, что вы ищете, состоит в том, чтобы иметь ваш оператор exec
в пакетном сценарии и вызывать этот пакетный сценарий из вашей функции. Что-то вроде этого:
create function <functionName>
exec master.sys.xp_cmpshell 'C:\storedProc.bat'
....
....
return @return
end
Подробнее о xp_cmpshell
в MSDN.
Вы не можете вызывать обычные хранимые процедуры из функций — только другие функции или некоторые расширенные хранимые процедуры. См. здесь статью о BOL (из SQL 2005). Попытка вызвать стандартную хранимую процедуру из UDF приведет к следующей ошибке...
Сообщение 557, уровень 16, состояние 2, строка 1. Из функции могут выполняться только функции и некоторые расширенные хранимые процедуры.
У меня недавно была аналогичная проблема. На самом деле сообщение об ошибке имеет неправильный формат, поскольку sp_executesql
является расширенной хранимой процедурой, что можно проверить с помощью следующего сценария: select objectproperty(object_id('sp_executesql'),'IsExtendedProc')
возвращает 1
Поскольку мы не можем использовать sp_executesql
, даже если это XP, мне пришлось найти другой обходной путь, используя sp_OAMethod
. Мой сценарий заключался в том, как динамически найти количество строк в таблице в соответствии с некоторыми критериями (значения null
в моем сценарии). Используя 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
Я знаю, что ваш случай немного отличается, но я уверен, что вы можете использовать этот udf в качестве руководства, чтобы помочь вам.
С наилучшими пожеланиями, Хосуе Монтейро Виана