Выполнение хранимой процедуры из функции

РЕДАКТИРОВАТЬ: Я не пробовал это, поэтому я не могу ручаться за это! И вы уже знаете, что не должны этого делать, поэтому, пожалуйста, не делайте этого. НО...

Попробуйте поискать здесь: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

Ключевым битом является этот бит, который я попытался настроить для ваших целей:

DECLARE @SQL varchar(500)

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

EXEC master..xp_cmdshell @SQL

Функции не могут иметь побочных эффектов, таких как изменение содержимого таблицы.

Хранимые процедуры есть.

Если бы функция вызывала хранимую процедуру, функция могла бы иметь побочные эффекты.

Итак, извините, но нет, вы не можете вызвать хранимую процедуру из функции.

Другим вариантом, помимо использования OPENQUERY и xp_cmdshell, является использование SQLCLR (функция SQL Server «Интеграция с CLR»). Вариант SQLCLR не только более безопасен, чем два других метода, но и дает потенциальную выгоду от возможности вызывать хранимую процедуру в текущем сеансе, чтобы она имела доступ к любому сеансу. на основе объектов или настроек, таких как:

• временные таблицы
• временные хранимые процедуры
• CONTEXT_INFO

Этого можно добиться, используя «context connection = true;» как ConnectionString. Просто имейте в виду, что все другие ограничения, наложенные на определяемые пользователем функции T-SQL, будут применяться (т. е. не могут иметь побочных эффектов).

Если вы используете обычное соединение (т.е. не используете контекстное соединение), то оно будет работать как независимый вызов, как и при использовании методов OPENQUERY и xp_cmdshell.

ОДНАКО имейте в виду, что если вы будете использовать функцию, которая вызывает хранимую процедуру (независимо от того, какой из 3 отмеченных методов вы используете) в операторе, который влияет более чем на 1 строку, то нельзя ожидать, что поведение будет запускаться один раз для каждой строки. Как упоминал @MartinSmith в комментарии к ответу @MatBailie, оптимизатор запросов не гарантирует ни время, ни количество выполнений функций. Но если вы используете его в операторе SET @Variable = function(); или запросе SELECT * FROM function();, то все должно быть в порядке.

Пример использования определяемой пользователем функции .NET/C# SQLCLR для выполнения хранимой процедуры показан в следующей статье (которую я написал):

Путь к SQLCLR уровня 2: пример хранимой процедуры и функции

Вот еще один возможный обходной путь:

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

Это не так страшно, как xp_cmdshell, но также имеет слишком много значений для практического использования.

Я нашел решение этой проблемы. Мы можем создать функцию или представление с «рендерингом» sql в хранимой процедуре, которая затем может выполняться как обычно.

1. Создайте еще один sproc

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

2. Создайте динамический sql, который вы хотите выполнить в своей функции (пример: вы можете использовать цикл и объединение, вы можете читать в другом sproc, использовать операторы и параметры if для условного sql и т. д.)

SET @innerSql = 'your sql'

3. Оберните @innerSql в операторе создания функции и определите любые внешние параметры, которые вы использовали в @innerSql, чтобы их можно было передать в сгенерированную функцию.

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


EXEC(@outerSql)

Это просто псевдокод, но решение решает многие проблемы, такие как ограничения связанного сервера, параметры, динамический sql в функции, динамическое имя сервера/базы данных/таблицы, циклы и т. д.

Вам нужно будет настроить его под свои нужды (пример: изменение возврата в функции)