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

← На главную

При интеграции 1С с внешними базами данных на базе MS SQL Server часто возникает задача не только передать данные в хранимую процедуру (ХП), но и получить обратный отклик. Это может быть статус выполнения, сообщение об ошибке или сформированный идентификатор (GUID). Рассмотрим по шагам, как реализовать этот механизм, используя технологию ADO (ActiveX Data Objects).

Постановка задачи и анализ ситуации

Представим типичную ситуацию: в конфигурации 1С есть обработка, которая подключается к SQL-серверу и вызывает процедуру docProvercaSclada. Процедура принимает ряд параметров и должна вернуть текстовое сообщение в переменную @msg. Проблема заключается в том, что при простом вызове через Connection.Execute результат работы процедуры остается внутри SQL-сервера, и 1С его «не видит».

Разберем три основных способа решения этой задачи: от самого простого до профессионального.

Способ №1: Использование результирующего набора данных (SELECT)

Это самый быстрый способ, если у вас есть возможность вносить изменения в текст хранимой процедуры на стороне SQL. Суть заключается в том, чтобы в конце тела процедуры выполнить команду SELECT, которая вернет значение выходного параметра как обычную таблицу.

Проанализируем изменения в SQL-процедуре:


ALTER PROCEDURE [dbo].[docProvercaSclada] 
    @doc UNIQUEIDENTIFIER OUTPUT, 
    @msg VARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON; -- Важно для ADO!
    
    -- Логика процедуры
    IF @doc IS NULL SET @msg = 'Ошибка: Документ не найден';
    
    -- В самом конце возвращаем значение
    SELECT @msg AS ReturnMessage;
END

Теперь рассмотрим, как получить это значение в 1С. Вместо обычного выполнения команды мы используем объект RecordSet:


ТекстКоманды = "EXEC docProvercaSclada @doc='...', @msg=''";
Выборка = Новый COMОбъект("ADODB.RecordSet");
Выборка = Соединение.Execute(ТекстКоманды);

Если НЕ Выборка.EOF() Тогда
    СообщениеИзSQL = Выборка.Fields("ReturnMessage").Value;
    Сообщить("Ответ от сервера: " + СообщениеИзSQL);
КонецЕсли;

Способ №2: Формирование динамического пакета SQL (Anonymous Block)

Если политикой безопасности запрещено менять саму хранимую процедуру, мы можем обернуть её вызов в небольшой скрипт прямо в тексте запроса из 1С. Мы объявим переменную в SQL, передадим её в процедуру как OUTPUT, а затем сразу выберем её значение.

Разберем пример кода для этого случая:


// Формируем текст запроса, который выполнит SQL
ТекстИнструкции = 
    "DECLARE @res_msg VARCHAR(500); " +
    "EXEC docProvercaSclada @doc=NULL, @msg=@res_msg OUTPUT; " +
    "SELECT @res_msg AS msg;";

Выборка = Соединение.Execute(ТекстИнструкции);

Если НЕ Выборка.EOF() Тогда
    Результат = Выборка.Fields("msg").Value;
    Сообщить("Результат выполнения: " + Результат);
КонецЕсли;

Этот метод удобен тем, что не требует сложной настройки объектов ADO, но он уязвим к ошибкам форматирования строк (например, если в строковых параметрах встретятся кавычки).

Способ №3: Использование коллекции Parameters (Профессиональный подход)

Самым надежным и правильным способом является работа с объектом Command и его коллекцией Parameters. Это позволяет избежать проблем с типами данных (особенно с GUID и DateTime) и защищает от SQL-инъекций.

Рассмотрим по шагам реализацию этого метода:

Создаем объект Command и связываем его с активным соединением.
Определяем тип команды как adCmdStoredProc (значение 4).
Создаем параметры с помощью CreateParameter.


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

// Создаем входной параметр (GUID)
// 72 - adGuid, 1 - adParamInput
Парам1 = Команда.CreateParameter("@doc", 72, 1, , GUIDДокумента);
Команда.Parameters.Append(Парам1);

// Создаем выходной параметр для сообщения
// 200 - adVarChar, 2 - adParamOutput, 500 - длина
ПарамMsg = Команда.CreateParameter("@msg", 200, 2, 500);
Команда.Parameters.Append(ПарамMsg);

// Выполняем
Команда.Execute();

// Получаем значение напрямую из коллекции параметров
ОтветСервера = Команда.Parameters("@msg").Value;
Сообщить(ОтветСервера);

Критически важные нюансы при работе с ADO и SQL

При реализации обмена данными важно учитывать технические особенности взаимодействия 1С и MS SQL Server, которые часто становятся причиной трудноотловимых ошибок.

1. Настройка SET NOCOUNT ON

Проанализируем работу SQL Server: по умолчанию после каждой операции INSERT или UPDATE сервер отправляет сообщение о количестве затронутых строк. ADO воспринимает эти сообщения как технические наборы данных. В итоге, когда вы пытаетесь прочитать RecordSet, вы получаете ошибку "Объект закрыт". Всегда добавляйте SET NOCOUNT ON; в начало вашей хранимой процедуры.

2. Работа с типом uniqueidentifier (GUID)

Выясним причину частых ошибок при передаче GUID. Если вы формируете запрос строкой, 1С передает GUID как "bb0343a4-0466-4890-a544-67258428a99d". В SQL-запросе эта строка обязательно должна быть в одинарных кавычках. Если же вы используете коллекцию Parameters, используйте тип adGuid (код 72), чтобы драйвер сам позаботился о бинарном представлении.

3. Обработка нескольких наборов записей

Если ваша процедура делает несколько вызовов SELECT, 1С увидит только первый. Чтобы переключиться на следующий набор (например, если в конце процедуры вы возвращаете статус), используйте метод NextRecordset() объекта Recordset.

4. Управление тайм-аутами

Посмотрим на параметры ConnectionTimeout и CommandTimeout. Для длительных операций на стороне SQL (например, проверка склада за большой период) стандартных 30 секунд может не хватить. Устанавливайте CommandTimeout в значение 0 (бесконечно) или достаточно большое число (600 и более), чтобы избежать аварийного завершения сеанса 1С.

5. Выбор провайдера

Для современных версий SQL Server (2012+) рекомендуется использовать провайдер MSOLEDBSQL вместо стандартного SQLOLEDB. Он лучше поддерживает современные типы данных и работает стабильнее с кодировками кириллицы.

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