При интеграции 1С с внешними базами данных MS SQL Server через стандартный механизм ВнешниеИсточникиДанных разработчики часто сталкиваются с проблемой: необходимо вызвать хранимую процедуру и получить из нее выходной параметр (например, код результата @Ret или сгенерированный идентификатор).
Разберем типичную ситуацию. Внешняя хранимая процедура в MS SQL Server имеет следующий вид:
CREATE PROCEDURE [dbo].[xxProd_xx]
@editMode INT,
@Ret INT = 0 OUTPUT
AS
BEGIN
SET @Ret = 0;
-- Выполнение логики...
IF @cName IS NULL
SET @Ret = -1;
ELSE
SET @Ret = 1;
END
Если в 1С в настройках функции внешнего источника данных установить флаг «Возвращает значение = Истина» и вызвать функцию кодом:
Результат = ВнешниеИсточникиДанных.WMS.xxProd_xx_1(editMode, Ret);
Платформа выдает ошибку СУБД:
Ошибка внешней базы данных: ошибка при выполнении запроса... SQLSTATE: 42000 Номер ошибки: 102. Описание: [Microsoft][ODBC SQL Server Driver][SQL Server]Неправильный синтаксис около конструкции ";"
Если же снять флаг «Возвращает значение», то процедура успешно выполняется, но переменная Ret в 1С не заполняется и результат выполнения остается неизвестным.
Выясним причину такого поведения и рассмотрим проверенные способы решения этой задачи.
Проанализируем работу платформы 1С при взаимодействии с внешними источниками данных:
Возвращает значение = Истина, платформа 1С считает объект скалярной или табличной функцией SQL. При вызове платформа генерирует запрос вида SELECT dbo.xxProd_xx(...). В СУБД MS SQL Server вызывать хранимые процедуры через инструкцию SELECT запрещено — это вызывает синтаксическую ошибку с кодом 102 (SQLSTATE 42000).Возвращает значение, платформа вызывает объект через EXEC dbo.xxProd_xx .... Однако стандартный механизм ВнешниеИсточникиДанных обрабатывает все передаваемые в 1С параметры строго как входные (IN). Значения OUTPUT-параметров СУБД не возвращаются обратно в переменные встроенного языка 1С.CREATE FUNCTION) не всегда возможна. Если внутри процедуры выполняются модификации данных (операторы INSERT, UPDATE, DELETE), SQL Server не позволит создать скалярную функцию, так как внутри функций запрещены операции с побочными эффектами.Для работы с хранимыми процедурами, имеющими OUTPUT-параметры или возвращающими коды RETURN, наиболее надежным и универсальным решением является использование технологии ADO через объект ADODB.Command.
Рассмотрим пошаговый пример кода на встроенном языке 1С для вызова хранимой процедуры с передачей входных и получением выходных параметров:
// 1. Создаем соединение с внешней базой данных MS SQL
Connection = Новый COMОбъект("ADODB.Connection");
CnnString = "Provider=SQLOLEDB;Data Source=ИмяСервера;Initial Catalog=ИмяБазы;User ID=Логин;Password=Пароль;";
Попытка
Connection.Open(CnnString);
Исключение
ВызватьИсключение "Ошибка подключения к БД: " + ОписаниеОшибки();
КонецПопытки;
// 2. Настраиваем команду для вызова хранимой процедуры
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "dbo.xxProd_xx";
Command.CommandType = 4; // adCmdStoredProc (Хранимая процедура)
// 3. Объявляем входные и выходные параметры
// Параметры метода CreateParameter: Имя, Тип, Направление, Размер, Значение
// Направление: 1 = adParamInput (входной), 2 = adParamOutput (выходной)
// Тип данных: 3 = adInteger
// Входной параметр editMode
Param1 = Command.CreateParameter("@editMode", 3, 1, , ЗначениеEditMode);
Command.Parameters.Append(Param1);
// Выходной параметр Ret
ParamRet = Command.CreateParameter("@Ret", 3, 2);
Command.Parameters.Append(ParamRet);
// 4. Выполняем процедуру
Попытка
Command.Execute();
// Получаем значение выходного параметра
РезультатRet = Command.Parameters("@Ret").Value;
Сообщить("Результат выполнения процедуры: " + Строка(РезультатRet));
Исключение
Сообщить("Ошибка при выполнении хранимой процедуры: " + ОписаниеОшибки());
КонецПопытки;
// 5. Закрываем соединение
Connection.Close();
Этот способ полностью решает проблему, позволяет явно задавать типы и направления параметров и корректно считывать возвращаемые значения.
Если есть возможность отредактировать код хранимой процедуры на стороне MS SQL Server, можно изменить способ возврата результата. Вместо применения OUTPUT-параметра можно в конце процедуры выполнять выборку данных с помощью инструкции SELECT:
ALTER PROCEDURE [dbo].[xxProd_xx]
@editMode INT
AS
BEGIN
DECLARE @Ret INT = 0;
-- Основная логика процедуры...
IF @cName IS NULL
SET @Ret = -1;
ELSE
SET @Ret = 1;
-- Возвращаем результат как набор данных
SELECT @Ret AS RetVal;
END
В этом случае из 1С процедуру можно вызвать с использованием выборки или набора записей через ADO (объект ADODB.Recordset) и прочитать значение колонки RetVal из первой строки результата.
Если требуется придерживаться архитектуры механизма ВнешниеИсточникиДанных без использования сторонних COM-объектов в коде 1С, применяется схема со служебной таблицей результатов:
LogResults со столбцами SessionID, RetVal, CreatedPeriod.ВнешниеИсточникиДанных.LogResults внешнего источника данных по SessionID и считывает полученный результат.Механизм ВнешниеИсточникиДанных в 1С предназначен преимущественно для чтения и записи табличных данных, а также вызова скалярных SQL-функций. Для полноценной работы с OUTPUT-параметрами и возвращаемыми кодами хранимых процедур MS SQL Server рекомендуется использовать COM-объект ADODB.Command.