Как получить результат выполнения SQL-функции с параметрами во внешнем источнике данных 1С

При работе с внешними базами данных через механизм Внешние источники данных (ВИД) программисты часто сталкиваются с необходимостью вызвать не просто статичную таблицу или представление (View), а полноценную SQL-функцию, принимающую параметры. Стандартное добавление функции в узел «Функции» метаданных 1С часто не дает нужного результата в языке запросов. Рассмотрим, как правильно настроить 1С для работы с табличными функциями и разберем нюансы, возникающие при получении данных.

Метод «Таблица-выражение» — основной способ решения

Для того чтобы 1С воспринимала SQL-функцию как источник данных, к которому можно обратиться с помощью обычного запроса, мы должны добавить её в список таблиц внешнего источника, но сделать это особым образом. Вместо прямого выбора объекта из списка таблиц БД, мы создадим виртуальный объект.

Разберем процесс настройки по шагам:

1. Откроем в конфигураторе дерево метаданных и найдем нужный ВнешнийИсточникДанных.
2. Перейдем к подразделу Таблицы и нажмем «Добавить».
3. В окне свойств новой таблицы обратим внимание на поле Вид таблицы. Вместо стандартного «Таблица» необходимо выбрать Выражение.
4. В поле Выражение в источнике напишем вызов нашей функции. Для SQL Server это будет выглядеть примерно так: dbo.f_get_moduls(&1).
5. Здесь символ &1 является заполнителем для первого параметра. Если функция принимает несколько аргументов, используем &2, &3 и так далее.

Важно: После ввода выражения обязательно воспользуемся кнопкой «Получить поля». 1С выполнит пробный запрос к базе (подставив пустые значения в параметры), определит структуру возвращаемой таблицы и автоматически создаст соответствующие поля в метаданных внешнего источника. Без этого шага обращение к таблице из кода будет невозможным.

Использование в языке запросов 1С

После того как мы настроили таблицу-выражение, работа с ней ничем не отличается от работы с обычными таблицами 1С. Мы можем использовать её в конструкторе запроса или писать текст запроса вручную. Параметры функции передаются как параметры виртуальной таблицы.

Проанализируем пример кода для получения данных:

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ВнешняяФункция.ИмяМодуля,
    |	ВнешняяФункция.Версия
    |ИЗ
    |	ВнешнийИсточникДанных.СБ_Тест.Таблица.f_get_moduls(&Параметр1) КАК ВнешняяФункция";

Запрос.УстановитьПараметр("Параметр1", "ЗначениеПоиска");
Результат = Запрос.Выполнить().Выгрузить();

В данном случае &Параметр1 будет автоматически подставлен платформой вместо &1, который мы указали в свойстве «Выражение в источнике».

Почему данных может не быть: проблема типов данных и кодировок

Иногда возникает ситуация: запрос отрабатывает без ошибок, но результат всегда пустой, хотя в SQL Management Studio при тех же параметрах данные есть. Выясним причину этой проблемы.

Чаще всего это связано с тем, что 1С передает строковые параметры в формате Unicode (тип nvarchar в SQL Server). Если в самой SQL-функции параметр объявлен как varchar (не-Unicode) и в базе данных используется специфическая кодировка (Collation), сервер SQL может не найти совпадений при неявном приведении типов.

Рассмотрим рекомендации по исправлению:

• Попробуем изменить тип входного параметра в самой SQL-функции на nvarchar(max) или соответствующую длину.
• Если изменить SQL-код нельзя, стоит проверить настройки Collation базы данных и убедиться, что они совместимы с передаваемыми данными.

Отладка с помощью SQL Profiler

Если мы не понимаем, почему данные не возвращаются, проанализируем ситуацию «глазами» SQL-сервера. Для этого воспользуемся инструментом SQL Server Profiler.

Нам необходимо отследить событие RPC:Completed. 1С не отправляет простой текст SELECT..., она вызывает системную процедуру sp_executesql, передавая в неё текст запроса и описание типов всех параметров.

Скопируем из профайлера весь блок кода, включая объявление переменных (DECLARE...), и запустим его в консоли SQL. Это позволит увидеть реальную ошибку или пустой результат, который генерирует сервер при получении запроса именно от 1С. Часто там можно увидеть, что 1С добавляет условие WHERE 0=1 (для проверки структуры) или оборачивает вызов функции во вложенный подзапрос, который SQL-сервер обрабатывает не так, как мы ожидали.

Ограничения и нюансы производительности

Разберем важные моменты, которые стоит учитывать при использовании таблиц-выражений:

1. Только чтение: Любые данные, полученные через функции или выражения во внешних источниках, доступны только для чтения. Попытка записи в такие объекты вызовет исключение.
2. Сложные соединения: Стараемся избегать соединений (JOIN) таблиц-выражений с объектами основной базы 1С в одном запросе. Платформа может попытаться выгрузить весь результат функции во временную таблицу на стороне 1С, что при большом объеме данных приведет к резкому падению производительности. Лучше сначала получить результат функции во временную таблицу запроса 1С (через МенеджерВременныхТаблиц) и работать уже с ней.
3. Inline vs Multi-statement функции: 1С лучше «дружит» с Inline Table-valued functions (которые содержат только один RETURN SELECT). Сложные многооператорные функции могут работать значительно медленнее при вызове через механизм ВИД из-за особенностей построения планов выполнения SQL-сервером.

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