Вызов хранимой процедуры sql из 1с с параметрами

Обновлено: 30.06.2024

Похоже действительно запрос должен выглядеть вот так:
select Поставщик, ИмяПоставщика, КодМаг, ДатаПриход, НомНакл, ДатаНакл from ankStr..Приходы
where Линия = :n and ДатаПриход between :ДатаПриходаС and :ДатаПриходаПо

Т.е. параметры должны идти не через @ а через :
После выполнения запроса в таком виде, начало ругаться на неизвестный параметр.
Вот как теперь мои параметры туда передать .

Народ помогите пожалуйста, вижу что осталось чуть чуть до решения.
Вот вся функция:

Функция ЗапросSQL (SQLServer,Login,Password,ТекстSQL) //Функция выборки данных из SQL сервера и передача во временную таблицу 1С

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

Param1 = СоединениеSQL.CreateParameter("n",3,1,1,1);
СоединениеSQL.Parameters.Append(Param1);

Param2 = СоединениеSQL.CreateParameter("ДатаПриходаС",200,1,8,Формат(НачалоПериода,"ДФ=ггггММдд"));//Формат(НачалоПериода,"ДФ=ггггММдд"));
СоединениеSQL.Parameters.Append(Param2);

Param3 = СоединениеSQL.CreateParameter("ДатаПриходаПо",200,1,8,Формат(КонецПериода,"ДФ=ггггММдд"));
СоединениеSQL.Parameters.Append(Param3);
//Сообщить (Param3.Value);

Состояние ("Выполнение запроса SQL. ");
ЗаписиSQL = Новый ComObject("ADODB.RecordSet");

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

Пока ЗаписиSQL.EOF = 0 Цикл // Заполнение созданной таблицы

НоваяСтрока = Таблица.Добавить();
Для НомерСтолбца = 0 По ЗаписиSQL.Fields.Count-1 Цикл
НоваяСтрока.Установить(НомерСтолбца,ЗаписиSQL.Fields(НомерСтолбца).Value);
Сообщить ("Test " + ЗаписиSQL.Fields(ИмяСтолбца).Value + ИмяСтолбца);
КонецЦикла;

Состояние ("Закрываем соединение с SQL. ");
ЗаписиSQL.Close();
Подключение.Close();

Оригинальный текст моей программы VBA Accces:

Да действительно работает .
Уважаемый подскажите пожалуйста почему Вы
ТекстЗапроса="select Поставщик, ИмяПоставщика, КодМаг, ДатаПриход, НомНакл, ДатаНакл from ankStr..Приходы
where Линия = @n and ДатаПриход between @ДатаПриходаС and @ДатаПриходаПо"
ПараметыЗапроса="@n int,@ДатаПриходаС datetime,@ДатаПриходаПо datetime"

Добавили в параметры.
Я пол инета перерыл нигде такого не встречал, и в описании нигде нет, если у Вас есть инфа по этому поводу поделитесь пожалуйста.

Если я правильно понял вы пытаетесь выполнить несколько команд tsql на одном вызове Execute(). АДО так не работает. Одна команда tsql - один execute. в вашем случае

2. А если у меня запросы связаны. Т.е. Результат 2го это данные 1го, а результат 3го это результат 2го.

СоединениеSQL.Execute(1й, запрос)
СоединениеSQL.Execute(2й, запрос)
СоединениеSQL.Execute(3й, запрос)

у меня будет результат 3го запроса ?

3. Если до select у меня есть код

Мой запрос отработает ?

Но наткнулся на след проблему.
Результат хранимой процедуры есть 2 таблицы.
На данный момент я вычитываю столбцы первой, но не могу прочитать данные
Т.е.

Убрал из хранимой процедуры 2ю таблицу.

На выходе есть колонки но нет данных Recordset.
В SQL хранимка трабатывает с данными.

Подскажите что я делаю не верно.

Для нескольких рекордсетов вам уже ответили. С хранимкой надобность в выполнении именно параметризированого запроса отпадает, так как параметры можно указать прямо в тескте запроса, выигрыша от использования sp_executesql уже не будет.

В прошлой статье речь шла об использовании BULK-операций для ускорения массовой загрузки / обновления данных в базе. В одном из примеров использовались внешние источники данных платформы 1С. Там было сказано о серьезных ограничениях этого механизма при работе с базой данных, а именно:

Отсутствует возможность вызова хранимых процедур с возвратом значений для OUTPUT-параметров.
Также нет возможности получить возвращаемый набор данных из хранимой процедуры.
Недоступно выполнение произвольных SQL-скриптов
И другие специфические ограничения.

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

Внимание! Все, что Вы увидите дальше - это воистину костыли, которых еще поискать! Не рекомендую использовать их на рабочем окружении, только если ну очень сильно нужно. В остальных случаях лучше использовать ADO.

Ниже не будет описания механизма источника данных, для этого обратитесь к официальной документации или другим статьям.

Полигон для испытаний

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

Также для примера будет использоваться конфигурация, исходный код которой можно будет найти на GitHub. Используемая версия платформы 8.3.13.1690, но думаю описанные подходы будут работать и на других версиях.

Просто напиши запрос

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

Источник включает в себя несколько таблиц и функцию. Не будем останавливаться на назначении каждого объекта, сейчас это добавлено только для примера. Позже Вы сами все увидите.

Для начала решим простую задачу - добавить таблицу, которая будет показывать текущие активные соединения и текст выполняемого запроса. Для SQL Server запрос может выглядеть так.

Получение списка активных соединений с текстом запроса

Запрос получения информации об активных соединениях, а также тексты SQL-запросов для них.

Для этих целей используются несколько системных таблиц (DMV).

Для этого добавим новую таблицу в источник, но в качестве вида таблицы указать "Выражение". В само выражение вставим SQL-запрос (см. под спойлером выше). Что же в таком простейшем примере может пойти не так? А вот что!

Идем в конфигуратор и видим странную картину.

Окей! Смирились, простили, сделали запрос одной строкой без переносов. Да, неудобно, но что поделать. Пробуем еще раз получить данные.

Что это опять за магия? Все дело в том, что платформа преобразует выражение таблицы к следующему виду.

Попробуем еще раз обратиться к таблице.

И, ура! Все получилось. Обратите внимание - мы поймали запрос, который сами и выполняем. Просто в тестовой базе больше никого нет :).

Вот такие особенности при использовании произвольных выражений в таблицах внешних источников данных. Наверняка многие с этим сталкивались и успешно обходили. Но по сравнению с остальными ограничениями и проблемами это лишь небольшая фича.

Где же возвращаемые параметры

Следующей интересной задачей будет вызов хранимой процедуры. Вроде все просто - добавляем функцию во внешний источник данных, определяем ее параметры, и она уже готова к вызову. Но хранимая процедура у нас не простая (по крайней мере для платформы 1С)! Она содержит выходные параметры, которые заполняются внутри самой процедуры при выполнении, а после возвращаются вызывающему коду.

В тестовой базе создана процедура с таким определением.

Вызвать эту процедуру без выходных параметров просто - нужно добавить функцию внешнего источника и определить выражение "dbo.ProcWithOutputParams(&1)".

Но нам это не подходит, т. к. не позволяет получить назад выходные параметры. Даже если попытаться определить их в выражении "dbo.ProcWithOutputParams(&1, &2, &3)", то при вызове платформа просто не вернет значения в переменные.

Как же быть и есть ли выход? Выход есть! К сожалению, он не такой элегантный и интуитивно понятный как использование ADO, но позволяет возвращать любые значений назад в код 1С из внешнего источника данных. Для начала нам понадобиться добавить в базу данных хранимую процедуру для произвольного выполнения команд.

Маленькая процедура для SQL Server.

Но большая процедура для внешних источников данных!

Эта процедура позволит выполнять произвольные TSQL-команды. Но это еще не все. Мы до сих пор не можем возвращать данные. Возьмем самый доступный в этой ситуации способ - будем использовать глобальную временную таблицу. в которую будем вставлять результаты команд и считывать их отдельным SELECT'ом. Вот так будет выглядеть эта таблица.

Глобальная временная таблица для сохранения результата команд

Скрипт создания глобальной временной таблицы для сохранения результатов SQL-команд.

Костыль, не отрицаю!

Для доступа к значению в этой таблице нужно знать идентификатор вызова. Чтобы упростить доступ можно добавить таблицу во внешний источник данных со следующим выражением.

После получения значения лучше всего его удалять из таблицы. Также есть нюанс - если попытаться обратиться к таблице до ее создания, то конечно же мы получим ошибку. Для корректной работы необходимо обрабатывать подобные исключения в конфигурации. Вернемся к примеру. Вот так теперь можно вызвать хранимую процедуру и получить результат.

Вызов хранимой процедуры с получением выходных параметров

Так выглядит сквозной пример вызова хранимой процедуры с получением результата.

Общий принцип работы алгоритма следующий:

Инициализируем идентификатор вызова, по которому потом можно будет получить результат. Также подготавливаем все необходимые параметры для процедуры (в нашем случае это один входной параметр "@inputParam" с типом INT).
Проводим инициализацию глобальной временной таблицы, если она еще не создана.
Выполняем SQL-команду для вызова хранимой процедуры с последующим преобразованием результата в XML, и сохранением в промежуточную таблицу.
Получаем значение XML из промежуточной таблицы и удаляем из нее полученное значение.
Выводим результат.

Вот некоторые пояснения к SQL-команде вызова процедуры.

Как итог, на стороне 1С у нас есть текст XML, который мы разобрали и получили выходные параметры хранимой процедуры. Остается только продолжить с ними работу (преобразовать к нужному типу, сохранить куда-либо и т.д.).

Код получился достаточно объемным. Можно инкапсулировать некоторую функциональность в общем модуле таким способом.

Общий модуль "ПомощникРаботыСВнешнимИсточникомДанных"

В общем модуле оставляем только один экспортный метод "ВыполнитьПроизвольныйСкрипт", остальное только для служебного использования.

В процедуру передается текст SQL-команды для выполнения, при этом в скрипте должен быть указан параметр "&ИдентификаторВызова", чтобы его можно было явно подменить перед выполнением. Если такого параметра в скрипте нет, то получить возвращаемое значение будет нельзя.

Алгоритмы можно еще улучшить, но для примера этого должно быть достаточно.

Теперь код вызова будет выглядеть более лаконично.

Теперь вызов хранимой процедуры выглядит проще.

Но работать через ADO все равно будет эффективней!

Вот и все. Мы добавили немного костылей и палок, теперь внешние источники данных можно использовать для более широкого спектра задач.

Вернуть набор из процедуры

С получением выходных параметров хранимых процедур мы разобрались, но есть и более сложный случай - получить набор записей, который эта процедура вернула. Например, есть служебная процедура "sp_who", которая возвращает текущую активность на сервере.

Как же нам получить этот набор данных через внешний источник на стороне 1С? Сделать SELECT к хранимой процедуре нельзя, нужен альтернативный вариант.

На самом деле все просто - модифицируем предыдущий пример и получим такую SQL-команду.

SQL-команда для получения набора записей хранимой процедуры

Самое важное - это передача результатирующего набора процедуры в табличную переменную, который потом можно преобразовать к XML.

Сам по себе способ преобразования значений к XML очень универсален, но имеет некоторые накладные расходы как на сериализацию, так и на десериализацию. Вот более подробная информация по инструкции "FOR XML".

Выполнение SQL-команды через внешний источник данных

Тут ничего нового не увидите, все аналогично предыдущему примеру.

Парсинг результата опустим, он не имеет для примера большого значения.

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

Выполнение любого скрипта

На самом деле мы получили возможность работать не только с хранимыми процедурами, но выполнять абсолютно любой SQL-скрипт и получать результат любого вида. Главное чтоб его можно было преобразовать в XML. В новых редакциях SQL Server результат можно возвращать также и в JSON-формате.

Например, с помощью внешнего источника данных теперь можно делать то, что раньше казалось недоступным!

Обновление статистики

Удаление таблицы

Удаление базы данных

Опасность

Подобный подход работы с базой имеет ряд существенных недостатков:

Сложность сопровождения, ведь вместо обычных SQL-скриптов приходиться предусматривать маневры для возврата значений на сторону 1С.
Множество избыточных действий могут влиять на производительность (использование временных таблиц, преобразование результатов запросов в XML и обратно и др.).
Большой удар по безопасности, т.к. теперь из кода 1С можно выполнить любую SQL-команду. Конечно, правами учетной записи SQL-сервера можно себя обезопасить, но для этого также потребуется время и ресурсы на настройку и сопровождение.
Также неграмотное составление SQL-команд может привести к неоптимальной работе SQL Server. Например, если вместо передачи значений в запрос использовать не параметры, а явное указание значения. В этом случае кэширование планов запросов не будет эффективно работать. Вот интересный материал про динамические SQL-запросы.

Вот и все

Может быть кому-нибудь это будет полезно, но используйте это только когда больше нет выхода. Вместо этих костылей лучше использовать ADO. Судите сами, вот так будет выглядеть вызов хранимой процедуры с выходными параметрами.

Так стоит ли усложнять? После этого все то, что мы делали выше, кажется бессмысленным. Но решать конечно же только Вам!

Затем создадим процедуру, которая будет вытаскивать данные. Процедура будет иметь два параметра: обязательный и необязательный. Обязательным будет дата, начиная с которой брать данные, а необязательным будет массив артикулов.

Процедура вытаскивает данные по параметрам во временную таблицу, выводит её и, после этого, дропает.

По ощущениям, работает быстрее чем просто запрос к внешнему источнику.

С SQL всё. Переходим к 1С

Подключение к SQL Сервер:

Установка параметров процедуры и её вызов:

Ну и заполнение ТЗ результатами вызова:

После этого с ТЗ уже делаем, что хотим.

Для примера выкладываю обработку и бэкап SQL базы.

Тестировалось на платформе 8.3.12.1685, 8.3.14.1944, 8.3.15.1747.

Использование хранимых процедур MS SQL Server в 1С Использование хранимых процедур MS SQL Server в 1С + гуманитарная помощь автору для совершенствования самообразования

Специальные предложения

Через внешние источники данных в связке с sql можно решить любую задачу. Зачем использовать такое решение совершенно не понятно (1)Если попробовать заджойнить две таблицы из внешнего источника - выйдет коллапс. Если всё-таки заставить это работать (это возможно) - будет намного медленней, чем если средствами самой СУБД (3) Вся логика отрабатывается в хранимой процедуре. Там джоинить можно что угодно. В 1С только финальный результат. Если нужно вернуть таблицу, то хранимая процедура возращает ID. Дальше SELECT по таблице уже в 1С, куда все написала процедура. Таблица в которую процедура должна написать результат подключается как внешний источник. Все идеально работает. (3) А почему не сделать вьюшку и обращаться именно к ней? (1) Попробуете сделать что нибудь масштабное через внешние источники данных - поймете :-) (6) Интеграцию любой степени сложности делал и не раз. И с хранимыми процедурами и с вьюшками и со скалярными функциями. Что угодно. Все идеально работает. Хранимые процедуры пишут в таблицу и возвращают ID. Эта таблица подключена к внешнему источнику и служит для "забрать результат". Что еще может быть проще ? (7) Попробуйте выгружать через внешние источники данных миллионы записей в MS SQL. (9) Если такие задачи возникают, то надо пересмотреть само приложение и подходы к работе с данными. Если надо что-то забирать из 1С, то тут лучше использовать ODATA. (11) миллиарды записей тоже ODATA? Интересно, как вы будете DWH строить с сотнями таблиц с контролем по ключам с инкрементальным обновлением. Внешние источники мертвы, к сожалению. (13) Просто не надо путать теплое с мягким. Внешние источники нужны именно для интеграции. Если требуется выгружать миллиарды записей, значит Вы ошиблись с софтом в принципе. Для реальных задач внешние источники очень полезный и простой инструмент. как вы будете DWH строить с сотнями таблиц с контролем по ключам с инкрементальным обновлением

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

(1) Если необходимо разрабатывать запрос с нуля, то возможно и выгоднее их использовать, но если требуется получить данные, которые уже можно выдрать с использованием хитрых хранимок - себе дороже. К тому же структура внешнего источника может дорабатываться. Использование хранимок позволяет переложить реализацию и контроль получения данных на тех, кто сопровождает внешнюю систему или как минимум получить консультацию, почему данные криво приходят.
Если не сложно - можете рассказать, как обновляете внешние источники (структуру) вкратце. В этом году пришлось много работать над интеграцией с ms sql server - показалось неудобно обновлять базу, после изменения внешнего источника (в расширение не пробовал переносить). (1) ограниченный взгляд, отрицание опыта человечества .
Берем данные из другой системы(oracle) используем при обработке и sql и pl-sql. (16) Что сказать то хотели ? Автор предложил корявый механизм работы с хранимыми процедурами. Через внешние источники данных в связке с sql можно решить любую задачу.

Однако далее в комментариях вы утверждаете что для любых задач, другие инструменты нужны :-)

Автор, а в чем смысл создавать таблицу?
Сначала создаешь таблицу, потом помещаешь туда выборку, потом получаешь все данные из этой таблицы, потом убиваешь таблицу.
Так ты возьми и просто получи данные из выборки.

Также непонятен смысл использования EXEC. Напиши сразу запрос выборки, зачем сначала формировать текст запроса в твоем ЭЛЕМЕНТАРНОМ примере, а потом его выполнять.

(5) Автор имеет ввиду, что хранимая процедура может делать что-то сложное и результат возвращать в табличном виде.
Есть только вариант вызывать функцию, которая вернет таблицу и это хорошо обыгрывается через внешние источники.
Но возможности функций в SQL значительно меньше, чем процедур.
В 1С через внешние источники нельзя вызвать процедуру с возвратом таблицы.
Вот это он хотел сказать. Просто самое решение очень корявое.

Использование хранимых процедур, как и любое подключение возможностей прямого доступа к данным - особая зона возможностей, на мой взгляд очень недооцененная.
Пример с хранимой процедурой из собственного опыта: была поставлена задача максимально быстрой синхронизации справочника "Номенклатура" в двух независимых базах (самостоятельные информационные системы разных организаций).
Самый быстрый вариант - в момент записи элемента в 1 базе, сразу-же создавать(изменять) соответствующий элемент в другой. Связь по ссылке. Т.к организации независимые и самостоятельные, раскрывать внутреннюю структуру и параметры подключения пользователей SQL с доступом к изменению данных - недопустимо. Кроме этого базы разделены территориально. В данном случае использование хранимой процедуры - идеальное решение, которое в описанном случае работает более 3х лет без единого сбоя.
Реализация:
1.В базе-приемнике создана хр. процедура, создающая(обновляющая) прямым запросом элемент номенклатуры по входным параметрам, переданным в процедуру. Возвращаемое значение - признак успешной загрузки;
2.В базе-приемнике создан пользователь, имеющий доступ только к хранимой процедуре;
3.В базе-источнике после записи нового(измененного) элемента выполняется прямое подключение к приемнику и запуск хр. процедуры с ключевыми параметрами записываемого элемента. Выполнение производится под пользователем с правами только к выполняемой процедуре, в запросе только имя процедуры и передаваемые параметры, структура данных скрыта, что и требуется по условиям задачи.

(12) а что будет, если в момент записи элемента справочника в одной базе будет недоступен канал связи между двумя базами?
Или такие риски не считаются?
Имхо, это решение - подходящее, но называть его идеальным - это слишком громко

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

У меня есть хранимая процедура, в которую я из 1С должен передать данные.
Запускаю в SQL ее вот в таком виде:
exec ЕЕ.dbo.ПН 333,'20090301','20090314'
и она дает результат.

Теперь мне необходимо из 1С обратится к ней со своими переменными не (333,'20090301','20090314' ), а какими нибудь другими.

Т.е. я из 1С должен влиять на результат выполнения хранимой процедуры своими данными 1С.
В данных момент, как я понимаю, я процедуру запускаю, но в нее не передаются параметры, поэтому у меня наименования столбцов есть, а данных нет.

Дабы не молоть чепуху, покажите код вашей процедуры, или хотя бы заголовок с парметрами (от "create procedure" до "as")

Для упрощения проверки работы обработки, вставил период за который не было документов, соответственно мне поля выводило а данные нет. ))

Извините что по этому поводу вам голову морочил.
В данный момент процедура имеет такой вид:

И она работает.
Параметры для передачи в хранимую процедуру, необходимы.

Приведите параметы вызова CreateParameter для дат. У вас, видимо, ошибка при создании типов параметров и передаются неправильные значения. Я боюсь, что дату из албанского языка передать как datetime будет сложно благодаря уникальной внутренней стрктуре даты в 1С.

(в примере с sp_execute дата передается как varchar(8), а уж sp_execute сам преобразует дату в datetime).

Ну, главное чтобы работало. :)
По поводу параметров. Они нужны когда вам нужно быстро и много раз выполнять один и тот же запрос с разными условиями. Использование параметров позволяет в этом случае более эффективно использовать ресурсы сервера. У вас же - открывается соединение, исполняется единственный раз запрос и соединение закрывается. Использование параметризированных запросов теряет всякий смысл. А раз так - много проще сфоримровать готовую строку запроса внутри 1С.

Читайте также: