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

Быстрый ответ

-- Меняем стратегию: притворимся хранимой процедурой
CREATE PROCEDURE CallMyStoredProcedure
AS
BEGIN
    -- Прячемся от функции
    EXEC MyActualStoredProcedure
END

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

-- Шпион в толпе
CREATE FUNCTION dbo.AccessStoredProcedureData()
RETURNS TABLE
AS
RETURN 
(
    SELECT * FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
    'EXEC MyDatabase.dbo.MyStoredProcedure')
)

Обратите внимание, что использование OPENROWSET требует разрешения ад хок распределённых запросов на вашем SQL-сервере, что в некоторых контекстах может быть не разрешено администратором базы данных.

Проникновение: SQLCLR

Если вы хотите преодолеть ограничения функций, обратите внимание на технологию SQLCLR (SQL Common Language Runtime), которая дает возможность выполнять хранимые процедуры внутри функций с помощью управляемого кода. Она также позволяет работать с объектами сессии с помощью настройки "context connection = true".

Важно помнить о возможных ограничениях, включая потенциальные трудности при работе с большими объемами данных. При выборе SQLCLR, очень важно уделить внимание безопасности, включая такие меры, как контроль доступа к коду, чтобы минимизировать возможные риски.

Тайный агент: Динамический SQL

Еще одним подходом доступным нам является маскировка — использование Динамического SQL в хранимых процедурах. Это позволяет маскировать функции под динамически формирующиеся запросы:

-- Секретная передача данных
CREATE PROCEDURE SimulateFunctionBehavior
AS
BEGIN
    -- Замаскированный код
    DECLARE @DynamicSQL NVARCHAR(MAX)
    SET @DynamicSQL = N'SELECT * FROM MyTable WHERE ...' -- Динамическая часть запроса
    EXEC sp_executesql @DynamicSQL
END

Команда EXEC() предоставляет возможность выполнения динамически сформированных запросов, сохраняя при этом возможность имитации безопасного чтения, характерного для функций, при всей мощности хранимой процедуры. Несмотря на возможность использования Расширенных Хранимых Процедур, рекомендуется поддерживать более низкий уровень профиля и рассматривать более безопасные варианты.

Оперативные указания

SQL-функции подобны управляющим офисам, предназначенным для базовых вычислений и возврата данных. Пытаясь превратить их в действующих героев (Хранимые Процедуры), мы нарушаем их специализированное назначение и размываем границы между детерминированностью и оперативной эффективностью.

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

Не забывайте, что даже лучшие оперативники действуют в рамках протоколов и осознают возможные последствия.

Визуализация

Для наглядности проведем аналогию со стройплощадкой:

• SQL-функция напоминает прораба: Организует и контролирует.
• Хранимая процедура — это строитель: выполняет реальную работу.

Прораб без строителя лишь планирует и контролирует, но не выполняет действительную работу.

Простое правило:

• Прораб (Функция): предоставляет план и контроль, но не вмешивается в действия, лишь даёт указания.
• Строитель (Хранимая Процедура): выполняет задачи, создает конструкции.

SQL-функции предназначены для выполнения вычислений и возвращения результатов, но не для выполнения процедур, в отличие от Хранимых Процедур.

Полезные материалы

1. CREATE FUNCTION (Transact-SQL) – SQL Server | Microsoft Learn — Полное руководство по функциям в SQL Server.
2. Хранимые процедуры (Database Engine) – SQL Server | Microsoft Learn — Руководство по хранимым процедурам SQL Server.
3. Могу ли я вызывать хранимую процедуру внутри функции в SQL Server 2008 – Stack Overflow — Обсуждение профессионалов на тему особенностей работы функций и процедур.
4. Извлечение списка SharePoint с Power Query – MSSQLTips — Пример использования Power Query в качестве мощного инструмента для извлечения данных с помощью хранимых процедур.
5. Обработка ошибок в SQL Server 2012 – Simple Talk — Руководство по управлению ошибками в хранимых процедурах и функциях SQL Server.