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

← На главную

При интеграции 1С со сторонними базами данных Microsoft SQL Server разработчики нередко сталкиваются с необходимостью получить выборку, которую формирует хранимая процедура. Однако при вызове такой процедуры через стандартный механизм платформы — внешние источники данных (ВИД) — система выдает ошибку: «Обращение к процедуре объекта как к функции». Разберем детально, почему возникает это исключение, с какими архитектурными ограничениями платформы 1С оно связано и какими путями можно решить задачу.

Выясним причину ошибки и ограничения механизма внешних источников данных

Проанализируем механику работы платформы 1С с объектами внешних баз данных. Ошибка проявляется по двум взаимосвязанным причинам:

Несоответствие синтаксиса встроенного языка: при добавлении процедуры внешнего источника данных в дерево метаданных конфигурации 1С определяет её именно как процедуру (объект, не возвращающий значение). Если в коде 1С вызов написан через оператор присваивания переменной вида Результат = ВнешниеИсточникиДанных.МояБаза.Таблицы.dbo_GetAll();, платформа выдает синтаксическую ошибку времени выполнения, так как ожидает вызов функции, а не процедуры.
Архитектурное ограничение платформы 1С: даже если мы вызовем данный метод корректно синтаксически (без присваивания значения), мы не получим результирующую таблицу. Внешние источники данных 1С фундаментально не поддерживают чтение табличных наборов строк (Recordset), возвращаемых оператором SELECT внутри процедур SQL Server. Хранимые процедуры в механизме ВИД рассчитаны исключительно на выполнение действий и возврат скалярных значений через выходные параметры (OUTPUT / INOUT).

Именно поэтому стандартный механизм ВИД не позволяет напрямую загрузить результат работы обычной процедуры SQL в таблицу значений 1С.

Вариант 1. Замена хранимой процедуры на представление (VIEW)

Рассмотрим наиболее правильный и удобный способ решения задачи со стороны архитектуры, если есть контакт с разработчиком внешней базы. Попросим автора базы данных оформить выборку в виде стандартного представления (View).

Часто в процедуру передаются параметры (например, дата начала и дата окончания периода). В представлении входные параметры объявить нельзя, однако в них нет необходимости: фильтрация переносится на сторону запроса 1С. Разберем, как это работает:

Автор внешней базы создает представление dbo.v_GetAll, содержащее исходный запрос без ограничений по датам в блоке WHERE.
В 1С мы подключаем данное представление как обычную таблицу внешнего источника данных.
При построении запроса в 1С накладываем стандартные условия отбора в секции ГДЕ. Оптимизатор MS SQL Server автоматически применит условия к базовым физическим таблицам и использует соответствующие индексы.

Посмотрим на пример запроса в 1С к подключенному представлению:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ВнешняяТаблица.Идентификатор КАК Идентификатор,
    |	ВнешняяТаблица.Период КАК Период,
    |	ВнешняяТаблица.Сумма КАК Сумма
    |ИЗ
    |	ВнешнийИсточникДанных.ВнешняяБаза.Таблица.v_GetAll КАК ВнешняяТаблица
    |ГДЕ
    |	ВнешняяТаблица.Период МЕЖДУ &ДатаНачала И &ДатаОкончания";

Запрос.УстановитьПараметр("ДатаНачала", НачалоДня(ТекущаяДата()));
Запрос.УстановитьПараметр("ДатаОкончания", КонецДня(ТекущаяДата()));

РезультатЗапроса = Запрос.Выполнить();
ТаблицаДанных = РезультатЗапроса.Выгрузить();

Вариант 2. Прямое обращение через COM-соединение (ADODB)

Если внешняя база строго закрыта для изменений, ее структуру нельзя модифицировать и добавить новое представление невозможно, откажемся от механизма внешних источников данных в пользу прямого подключения через ADO (ActiveX Data Objects). Этот метод полноценно считывает табличный курсор (Recordset), формируемый любой хранимой процедурой.

Разберем по шагам реализацию выборки через объект ADODB.Connection:


СтрокаПодключения = "Provider=SQLOLEDB;Data Source=ИмяСервера;Initial Catalog=ИмяБазы;User ID=Логин;Password=Пароль;";

Соединение = Новый COMОбъект("ADODB.Connection");
Попытка
    Соединение.Open(СтрокаПодключения);
Исключение
    ВызватьИсключение "Не удалось подключиться к MS SQL: " + ОписаниеОшибки();
КонецПопытки;

Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandType = 4; // adCmdStoredProc
Команда.CommandText = "dbo_GetAll";

// Создаем параметры процедуры: Имя, ТипДанных, Направление, Размер, Значение
ПараметрДатаНачала = Команда.CreateParameter("@StartDate", 135, 1, , Формат(ДатаНачала, "ДФ=yyyyMMdd"));
ПараметрДатаКонца  = Команда.CreateParameter("@EndDate", 135, 1, , Формат(ДатаОкончания, "ДФ=yyyyMMdd"));

Команда.Parameters.Append(ПараметрДатаНачала);
Команда.Parameters.Append(ПараметрДатаКонца);

НаборЗаписей = Команда.Execute();

ТаблицаРезультат = Новый ТаблицаЗначений;
Для НомерКолонки = 0 По НаборЗаписей.Fields.Count - 1 Цикл
    ТаблицаРезультат.Колонки.Добавить(НаборЗаписей.Fields(НомерКолонки).Name);
КонецЦикла;

Пока Не НаборЗаписей.EOF Цикл
    НоваяСтрока = ТаблицаРезультат.Добавить();
    Для НомерКолонки = 0 По НаборЗаписей.Fields.Count - 1 Цикл
        НоваяСтрока[НомерКолонки] = НаборЗаписей.Fields(НомерКолонки).Value;
    КонецЦикла;
    НаборЗаписей.MoveNext();
КонецЦикла;

НаборЗаписей.Close();
Соединение.Close();

Вариант 3. Использование промежуточной базы или буферной таблицы

Если доступ к целевой базе закрыт, но у нас есть административные права на самом сервере SQL Server, проанализируем обходные серверные решения:

База-прослойка с локальным Linked Server: создаем на том же экземпляре MS SQL новую сервисную базу. Настраиваем петлевой связанный сервер (Loopback Linked Server) и создаем представление, использующее функцию OPENQUERY. Конструкция вида SELECT * FROM OPENQUERY(LocalServer, 'EXEC TargetDB.dbo.dbo_GetAll') позволяет обернуть результат работы процедуры в обычную таблицу, которую 1С прочитает через ВИД без ошибок.
Буферная таблица по расписанию: создаем отдельную физическую таблицу в разрешенной базе и настраиваем регламентное задание SQL Server Agent. Задание с нужной периодичностью вызывает INSERT INTO BufferTable EXEC dbo_GetAll. В 1С подключаем уже саму таблицу кэша.

Почему не подходит табличная функция (TVF)?

На форумах часто советуют переписать процедуру в табличную функцию (Table-Valued Function). Выясним, почему на практике это создает новые сложности. В 1С табличную функцию можно подключить к внешнему источнику только как таблицу с типом «Выражение». Однако встроенный язык запросов 1С не позволяет передавать динамические параметры запроса (через символ &) непосредственно в выражение секции ИЗ. В результате передать произвольные даты сеанса внутрь параметризованной функции SQL стандартным запросом 1С становится практически невозможно.

Что написать автору внешней базы данных?

Если мы согласовываем доработку с владельцем внешней базы, сформируем технический запрос следующим образом:

Объясним техническое ограничение: платформа 1С через штатный компонент внешних источников данных не умеет принимать результирующий набор строк (Recordset), генерируемый хранимой процедурой.
Попросим предоставить данные в виде обычного представления (VIEW) без жестко зашитых условий по датам. Уточним, что фильтрацию по диапазону дат 1С наложит самостоятельно при выполнении запроса, и сервер SQL эффективно обработает индексы.
Если процедура содержит сложную многоэтапную логику с временными таблицами и циклами (из-за чего создание VIEW невозможно), согласуем создание промежуточной таблицы выгрузки либо предоставим учетную запись для прямого обращения через ADODB.