Как вызвать хранимую процедуру SQL с параметрами из 1С через ADO и исправить ошибку 'Объект закрыт'?

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

Проанализируем ситуацию. Ошибка возникает в цикле обхода объекта RecordSet, например, в строке Пока RecordSet.EOF = 0 Цикл. Это прямо указывает на то, что хранимая процедура на стороне SQL Server не вернула набор данных. Объект RecordSet оказался "закрытым" или пустым. Чаще всего причина кроется в том, что процедура завершилась с ошибкой из-за некорректно переданных параметров, либо она в принципе не предназначена для возврата данных (например, выполняет только операции INSERT или UPDATE).

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

Решение 1: Правильное использование коллекции Parameters (рекомендуемый способ)

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

Разберем по шагам, как правильно настроить вызов:

1. Установить правильный тип команды. Чтобы ADO-провайдер понял, что мы вызываем именно хранимую процедуру, а не текстовый запрос, необходимо установить свойство Command.CommandType в значение 4 (константа adCmdStoredProc). В исходном коде стояло значение 8 (adCmdUnknown), что заставляло провайдера "гадать" и могло приводить к ошибкам.
2. Создать параметры. Для каждого параметра хранимой процедуры создается отдельный COM-объект ADODB.Parameter с помощью метода Command.CreateParameter().
3. Задать атрибуты параметра. При создании параметра важно корректно указать его имя (как в SQL, обычно с символом "@"), тип данных, направление (входной/выходной), размер и значение.
4. Добавить параметр в коллекцию. Это критически важный шаг, который часто пропускают. Созданный объект параметра нужно обязательно добавить в коллекцию параметров команды с помощью метода Command.Parameters.Append().

Посмотрим на исправленный пример кода, основанный на этом подходе. Обратите внимание на тип команды и типы данных параметров.

// Устанавливаем соединение (ADOConnection уже должен быть создан и открыт)
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADOConnection;

// 1. Указываем имя процедуры
Command.CommandText = "PR_Print_SummaryStatement";

// 2. Указываем тип команды - хранимая процедура (adCmdStoredProc)
Command.CommandType = 4;

// 3. Создаем и добавляем параметр-строку (GUID)
// Тип 200 (adVarChar) является более универсальным для строк, чем 129 (adChar)
// 1 - направление параметра (adParamInput)
ParamOrg = Command.CreateParameter("@OrganizationID", 200, 1, 36, "73167096-974F-495E-9CED-6885E07408AB");
Command.Parameters.Append(ParamOrg);

// 4. Создаем и добавляем параметр-дату
// Тип 7 (adDate)
ParamStart = Command.CreateParameter("@DateStart", 7, 1, , Дата(2012,03,01));
Command.Parameters.Append(ParamStart);

// 5. Создаем и добавляем второй параметр-дату
ParamEnd = Command.CreateParameter("@DateEnd", 7, 1, , Дата(2012,03,20));
Command.Parameters.Append(ParamEnd);

// 6. Выполняем команду
RecordSet = Command.Execute();

// 7. Обрабатываем результат
Если НЕ RecordSet.EOF Тогда
    Пока НЕ RecordSet.EOF Цикл
        // Получаем данные из полей, например:
        // ЗначениеПоля = RecordSet.Fields("ИмяКолонки").Value;
        // Сообщить(ЗначениеПоля);
        RecordSet.MoveNext();
    КонецЦикла;
Иначе
    Сообщить("Хранимая процедура не вернула данных.");
КонецЕсли;

RecordSet.Close();

Важный момент: всегда проверяйте, что возвращаемый RecordSet не находится в состоянии EOF (End Of File) перед началом цикла. Это более надежный способ, чем Пока RecordSet.EOF = 0 Цикл, и он предотвратит ошибку, если процедура по какой-то причине вернет пустой набор данных.

Решение 2: Формирование текстовой команды с EXEC

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

Посмотрим на реализацию:

1. Подготовить параметры. Все параметры, включая даты, необходимо преобразовать в строковый формат, понятный для SQL Server. Даты лучше всего форматировать в универсальный формат 'ггггММдд'. Строковые параметры и даты в запросе должны быть обрамлены одинарными кавычками.
2. Сформировать текст запроса. С помощью конкатенации (сложения строк) собираем итоговую команду.
3. Установить тип команды. В этом случае тип команды должен быть 1 (adCmdText), так как для ADO это обычный текстовый запрос.

Пример кода:

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADOConnection;

// 1. Указываем тип команды - текстовый запрос (adCmdText)
Command.CommandType = 1;

// 2. Подготавливаем параметры в строковом виде
ParamOrgID = "73167096-974F-495E-9CED-6885E07408AB";
ParamDateStart = Формат(Дата(2012,03,01), "ДФ=yyyyMMdd");
ParamDateEnd = Формат(Дата(2012,03,20), "ДФ=yyyyMMdd");

// 3. Формируем текст запроса. Обратите внимание на одинарные кавычки вокруг строковых параметров
ТекстЗапроса = "EXEC PR_Print_SummaryStatement '" + ParamOrgID + "', '" + ParamDateStart + "', '" + ParamDateEnd + "'";

Command.CommandText = ТекстЗапроса;

// 4. Выполняем
RecordSet = Command.Execute();

// 5. Обрабатываем результат
Пока НЕ RecordSet.EOF Цикл
    // ... обработка данных
    RecordSet.MoveNext();
КонецЦикла;

RecordSet.Close();

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

Таким образом, мы рассмотрели два рабочих подхода. Первый, с использованием коллекции Parameters, является более профессиональным и безопасным. Второй, с формированием строки EXEC, — более быстрый и простой для несложных задач.