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

Ответ 1

EDIT: Я не пробовал это, поэтому я не могу ручаться за это! И вы уже знаете, что не должны этого делать, поэтому, пожалуйста, не делайте этого. НО...

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

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

DECLARE @SQL varchar(500)

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

EXEC master..xp_cmdshell @SQL

Ответ 2

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

Сохраненные процедуры.

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

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

Ответ 3

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

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

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

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

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

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

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

Ответ 4

Вот еще одно возможное обходное решение:

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, но также имеет слишком много последствий для практического использования.

Ответ 5

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

1.Создайте еще одного спрока

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 в функции, динамическое имя сервера/базы данных/таблицы, циклы и т.д.

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

Ответ 6

после того, как я прочитал ответы, я не уверен, как использовать sp_xml_createdocument @idoc OUTPUT, @xml в функции :(

Я открыл курсор и внутри я SELECT...FROM OPENXML(@idoc) процедуру, чтобы получить ее вывод в SELECT...FROM OPENXML(@idoc).

Любая помощь, пожалуйста?

Мне нужно запустить его как функцию: SELECT * FROM dbo.name_of_function_with_the_procedure(param1,param2)