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

← На главную

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

Взаимодействие с SQL Server через ADO-объекты в 1С

Прежде всего, давайте выясним, что такое ADO-объекты и почему именно они позволяют нам решать эту задачу. ADO (ActiveX Data Objects) — это набор COM-объектов, предоставляющих универсальный и высокопроизводительный доступ к данным из различных источников. В контексте 1С, работа с COM-объектами, такими как ADODB.Connection и ADODB.Command, является стандартным механизмом и не требует использования внешних компонент (ВК) в традиционном понимании 1С. Эти объекты доступны в системе при наличии установленных компонентов доступа к данным (например, MDAC или SQL Server Native Client).

Использование ADO позволяет нам выполнять произвольные SQL-запросы, включая DDL-операции (Data Definition Language), такие как создание, изменение и удаление объектов базы данных (таблиц, индексов, хранимых процедур, представлений), а также DML-операции (Data Manipulation Language) для выборки, вставки, обновления и удаления данных.

Установление соединения с SQL Server из 1С

Первый шаг в нашей задаче — это установка стабильного соединения с базой данных SQL Server. Мы будем использовать объект ADODB.Connection для этой цели. Он является центральным элементом для любого взаимодействия с базой данных через ADO.

Давайте посмотрим на основные шаги и пример кода:

Создание объекта соединения: Мы инициализируем новый COM-объект ADODB.Connection.
Формирование строки подключения: Строка подключения (Connection String) содержит все необходимые параметры для соединения: драйвер (провайдер), адрес сервера, имя базы данных, а также учетные данные пользователя SQL Server. Крайне важно, чтобы пользователь, чьи данные используются в строке подключения, обладал достаточными правами на SQL Server для выполнения всех необходимых операций (в нашем случае — создания, выполнения и удаления хранимых процедур).
Открытие соединения: После формирования строки подключения мы открываем соединение методом Open().

Рассмотрим пример кода 1С для установки соединения:


// Объявляем переменные для работы с ADO
Перем ADOСоединение;
Перем ADOКоманда;

Попытка
    // 1. Создание объекта соединения
    ADOСоединение = Новый COMОбъект("ADODB.Connection");

    // 2. Формирование строки подключения
    // Используем SQL Server Native Client для лучшей производительности и функциональности.
    // Замените данные в строке подключения на свои!
    СтрокаПодключения = "Provider=SQLNCLI11;" + // Или другой подходящий провайдер, например SQLOLEDB
                        "Server=ВашSQLСервер;" + // Имя или IP-адрес SQL Server
                        "Database=ИмяВашейБазыДанных;" + // Имя базы данных, к которой подключаемся
                        "UID=ВашSQLПользователь;" + // Логин SQL-пользователя
                        "PWD=ВашПарольSQLПользователя;"; // Пароль SQL-пользователя

    // Важно: Убедитесь, что у указанного SQL-пользователя есть необходимые права!

    // 3. Открытие соединения
    ADOСоединение.Open(СтрокаПодключения);
    Сообщить("Соединение с SQL Server успешно установлено.");

Исключение
    Сообщить("Ошибка при установке соединения с SQL Server: " + ОписаниеОшибки());
    Если ADOСоединение <> Неопределено Тогда
        Попытка
            ADOСоединение.Close();
        Исключение
        КонецПопытки;
    КонецЕсли;
    Возврат; // Выходим из процедуры в случае ошибки
КонецПопытка;

Обратите внимание на строку подключения. Она является критически важной. Вы можете использовать различные провайдеры, такие как SQLOLEDB (стандартный OLE DB Provider for SQL Server) или SQLNCLI11 (SQL Server Native Client 11.0, который часто предпочтительнее для более новых версий SQL Server из-за расширенной функциональности и лучшей производительности). Обязательно замените значения ВашSQLСервер, ИмяВашейБазыДанных, ВашSQLПользователь и ВашПарольSQLПользователя на актуальные данные вашей среды.

Создание хранимой процедуры SQL Server из 1С

После того как соединение установлено, мы можем перейти к созданию хранимой процедуры. Для этого нам понадобится объект ADODB.Command. Этот объект позволяет нам определять SQL-команды и выполнять их через открытое ADO-соединение.

Создание объекта команды: Мы инициализируем новый COM-объект ADODB.Command.
Привязка команды к соединению: Мы указываем, через какое соединение будет выполняться команда, присваивая его свойству ActiveConnection.
Определение текста SQL-команды: Текст SQL-команды для создания процедуры присваивается свойству CommandText. В данном случае это будет оператор CREATE PROCEDURE.
Выполнение команды: Мы выполняем команду методом Execute().

Давайте рассмотрим пример SQL-кода для создания простой хранимой процедуры, которая может принимать входные параметры и возвращать выходные:


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

ТекстSQLЗапроса = "
IF OBJECT_ID('dbo.ТестоваяХранимаяПроцедура', 'P') IS NOT NULL
    DROP PROCEDURE dbo.ТестоваяХранимаяПроцедура;
GO

CREATE PROCEDURE dbo.ТестоваяХранимаяПроцедура
    @ВходнойПараметр NVARCHAR(255),
    @ВыходнойПараметр INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON; -- Подавляет вывод количества затронутых строк
    
    -- Выводим сообщение, используя входной параметр
    SELECT 'Привет из хранимой процедуры! Получено: ' + @ВходнойПараметр AS Сообщение;
    
    -- Устанавливаем значение выходного параметра
    SET @ВыходнойПараметр = LEN(@ВходнойПараметр); -- Например, длина входной строки
END;
GO
";

В приведенном выше SQL-коде мы сначала проверяем существование процедуры с именем ТестоваяХранимаяПроцедура и удаляем ее, если она существует. Это обеспечивает идемпотентность нашего скрипта, позволяя повторно создавать процедуру без ошибок. Затем мы определяем саму процедуру с одним входным параметром @ВходнойПараметр и одним выходным параметром @ВыходнойПараметр.

Теперь интегрируем это в код 1С:


Попытка
    // 1. Создание объекта команды
    ADOКоманда = Новый COMОбъект("ADODB.Command");

    // 2. Привязка команды к соединению
    ADOКоманда.ActiveConnection = ADOСоединение;

    // 3. Определение текста SQL-команды
    // Важно: для выполнения DDL-операций, таких как CREATE PROCEDURE,
    // ADO может потребовать разделения команд 'GO' на отдельные вызовы.
    // Проще всего выполнить скрипт целиком, если провайдер поддерживает MultipleActiveResultSets=True
    // или разбить его на части по 'GO'. Для простых CREATE/DROP обычно хватает одного Execute.

    ТекстSQLЗапросаСозданияПроцедуры = "
    IF OBJECT_ID('dbo.ТестоваяХранимаяПроцедура', 'P') IS NOT NULL
        DROP PROCEDURE dbo.ТестоваяХранимаяПроцедура;
    CREATE PROCEDURE dbo.ТестоваяХранимаяПроцедура
        @ВходнойПараметр NVARCHAR(255),
        @ВыходнойПараметр INT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT 'Привет из хранимой процедуры! Получено: ' + @ВходнойПараметр AS Сообщение;
        SET @ВыходнойПараметр = LEN(@ВходнойПараметр);
    END;
    ";
    
    ADOКоманда.CommandText = ТекстSQLЗапросаСозданияПроцедуры;
    
    // 4. Выполнение команды
    // Для команд, не возвращающих результирующий набор, можно использовать adExecuteNoRecords
    // для оптимизации, хотя в 1С это не всегда явно поддерживается через COM-объект напрямую.
    // Простого вызова Execute() достаточно.
    ADOКоманда.Execute();
    Сообщить("Хранимая процедура 'ТестоваяХранимаяПроцедура' успешно создана.");

Исключение
    Сообщить("Ошибка при создании хранимой процедуры: " + ОписаниеОшибки());
    // Закрываем соединение в случае ошибки
    Если ADOСоединение <> Неопределено Тогда
        Попытка
            ADOСоединение.Close();
        Исключение
        КонецПопытки;
    КонецЕсли;
    Возврат;
КонецПопытка;

Важный момент: Директива GO в T-SQL используется для разделения пакетов команд. Некоторые ADO-провайдеры могут некорректно обрабатывать скрипты, содержащие GO, как единый блок. В таком случае, вам может потребоваться разбить ваш SQL-скрипт на части по GO и выполнять каждую часть отдельным вызовом ADOКоманда.Execute(). Однако для простых CREATE PROCEDURE без GO внутри обычно достаточно одного вызова.

Выполнение хранимой процедуры из 1С

После успешного создания процедуры нам, конечно же, нужно ее выполнить. Выполнение хранимой процедуры через ADO может быть реализовано несколькими способами, в зависимости от того, возвращает ли процедура результирующие наборы данных и использует ли параметры. Мы рассмотрим вариант с использованием объекта ADODB.Command и параметрами.

Настройка объекта команды для вызова процедуры: Мы снова используем ADODB.Command, но теперь настраиваем его для вызова хранимой процедуры.
Добавление параметров: Если процедура принимает параметры, мы должны явно их определить и добавить в коллекцию Parameters объекта ADOКоманда. Это включает в себя указание имени, типа, направления (входной, выходной, возвращаемое значение) и значения для входных параметров.
Выполнение команды: После настройки всех параметров мы выполняем команду. Если процедура возвращает результирующий набор, Execute() вернет объект ADODB.Recordset.

Рассмотрим пример кода 1С для выполнения созданной ранее процедуры:


Перем ADOЗапись; // Для получения результирующего набора

Попытка
    // Снова используем наш объект команды
    // Указываем, что тип команды - это вызов хранимой процедуры
    ADOКоманда.CommandType = 4; // adCmdStoredProc = 4 (поиск по имени процедуры)
                               // adCmdText = 1 (для обычного SQL текста)
    ADOКоманда.CommandText = "dbo.ТестоваяХранимаяПроцедура";

    // Очищаем коллекцию параметров от предыдущих вызовов (если были)
    ADOКоманда.Parameters.Clear();

    // Добавляем входной параметр
    // ADO Type constants: adVarWChar = 202 (для NVARCHAR), adInteger = 3 (для INT)
    // ADO Direction constants: adParamInput = 1, adParamOutput = 2
    ADOКоманда.Parameters.Append(ADOКоманда.CreateParameter("ВходнойПараметр", 202, 1, 255, "Пример строки для процедуры"));

    // Добавляем выходной параметр
    // Значение для выходного параметра устанавливать не нужно, оно будет заполнено процедурой.
    ADOКоманда.Parameters.Append(ADOКоманда.CreateParameter("ВыходнойПараметр", 3, 2));

    // Выполняем процедуру
    ADOЗапись = ADOКоманда.Execute();
    Сообщить("Хранимая процедура 'ТестоваяХранимаяПроцедура' успешно выполнена.");

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

    // Получаем значение выходного параметра
    Сообщить("Значение выходного параметра: " + ADOКоманда.Parameters("ВыходнойПараметр").Value);

Исключение
    Сообщить("Ошибка при выполнении хранимой процедуры: " + ОписаниеОшибки());
    Если ADOЗапись <> Неопределено Тогда
        Попытка
            ADOЗапись.Close();
        Исключение
        КонецПопытки;
    КонецЕсли;
    // Закрываем соединение в случае ошибки
    Если ADOСоединение <> Неопределено Тогда
        Попытка
            ADOСоединение.Close();
        Исключение
        КонецПопытки;
    КонецЕсли;
    Возврат;
КонецПопытки;

В этом блоке кода мы сначала устанавливаем CommandType в значение 4 (adCmdStoredProc), чтобы указать ADO, что мы хотим выполнить хранимую процедуру по ее имени. Затем мы очищаем коллекцию параметров и добавляем каждый параметр по порядку, указывая его имя, ADO-тип данных, направление (входной adParamInput или выходной adParamOutput) и размер. После выполнения процедуры, если она возвращает результирующий набор, мы можем перебрать его с помощью объекта ADODB.Recordset. И, конечно, мы считываем значение выходного параметра.

Удаление хранимой процедуры из 1С

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

Подготовка команды для удаления: Мы снова используем ADOКоманда.
Определение текста SQL-команды: Устанавливаем CommandText на DROP PROCEDURE.
Выполнение команды: Выполняем команду методом Execute().

Пример кода 1С для удаления хранимой процедуры:


Попытка
    // Снова настраиваем объект команды для выполнения SQL-текста
    ADOКоманда.CommandType = 1; // adCmdText = 1

    // Очищаем параметры, так как для DROP PROCEDURE они не нужны
    ADOКоманда.Parameters.Clear();

    // Текст SQL-команды для удаления процедуры
    ТекстSQLЗапросаУдаленияПроцедуры = "DROP PROCEDURE dbo.ТестоваяХранимаяПроцедура;";
    ADOКоманда.CommandText = ТекстSQLЗапросаУдаленияПроцедуры;

    // Выполнение команды
    ADOКоманда.Execute();
    Сообщить("Хранимая процедура 'ТестоваяХранимаяПроцедура' успешно удалена.");

Исключение
    Сообщить("Ошибка при удалении хранимой процедуры: " + ОписаниеОшибки());
КонецПопытка;

// Обязательно закрываем соединение и освобождаем объекты ADO в конце!
Если ADOЗапись <> Неопределено Тогда
    Попытка
        ADOЗапись.Close();
        ADOЗапись = Неопределено;
    Исключение
    КонецПопытки;
КонецЕсли;

Если ADOСоединение <> Неопределено Тогда
    Попытка
        ADOСоединение.Close();
        ADOСоединение = Неопределено;
    Исключение
    КонецПопытки;
КонецЕсли;

Если ADOКоманда <> Неопределено Тогда
    ADOКоманда = Неопределено;
КонецЕсли;

Сообщить("Все ADO-объекты освобождены и соединение закрыто.");

После всех операций крайне важно закрыть соединение с базой данных методом Close() объекта ADODB.Connection и освободить все ADO-объекты (присвоить им значение Неопределено). Это гарантирует, что ресурсы базы данных будут освобождены, и не возникнет утечек памяти или блокировок.

Необходимые права доступа

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

Создание процедуры (CREATE PROCEDURE): Для создания процедуры пользователь должен иметь разрешение CREATE PROCEDURE в базе данных, а также разрешение ALTER на схему, в которой создается процедура (например, dbo), или разрешение CONTROL на саму базу данных.
Выполнение процедуры (EXECUTE): Для выполнения процедуры пользователь должен иметь разрешение EXECUTE на эту конкретную процедуру.
Удаление процедуры (DROP PROCEDURE): Для удаления процедуры пользователь должен иметь разрешение ALTER на схему, которой принадлежит процедура, или разрешение CONTROL на саму процедуру.

Мы настоятельно рекомендуем предоставлять минимально необходимые права доступа (принцип наименьших привилегий). Избегайте использования ролей db_owner, sysadmin или других высокопривилегированных ролей для пользователя, через которого 1С подключается к SQL Server. Вместо этого создайте специализированного SQL-пользователя и предоставьте ему только те разрешения, которые абсолютно необходимы для выполнения ваших задач.

Дополнительные рекомендации и риски

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

Преимущества:

Гибкость и динамичность: Мы получаем возможность адаптировать логику на уровне БД под изменяющиеся условия без необходимости изменения схемы базы данных вручную или полного обновления конфигурации 1С.
Инкапсуляция логики: Хранимые процедуры являются отличным способом инкапсуляции сложной бизнес-логики и запросов на уровне базы данных, что может повысить производительность за счет снижения сетевого трафика и повторного использования планов выполнения.
Транзакционность: Использование хранимых процедур позволяет нам упаковывать сложную логику, изменяющую данные в нескольких таблицах, в единую транзакцию, обеспечивая принципы ACID (атомарность, согласованность, изоляция, долговечность).

Риски и недостатки:

Сложность версионирования, отладки и тестирования: Код хранимых процедур, созданных динамически, гораздо сложнее версионировать, отлаживать и тестировать, чем код, который является частью системы контроля версий проекта или конфигурации 1С. Отладка на уровне SQL Server требует специфических инструментов и навыков.
Проблемы безопасности (SQL Injection): Если SQL-код для создания или выполнения процедур формируется на основе данных, поступающих извне (например, от пользователя), возникает высокий риск атак типа SQL Injection. Необходимо крайне тщательно очищать и валидировать все входные данные.
Снижение сопровождаемости: Динамически созданные объекты БД не видны в стандартных средствах разработки 1С и не управляются механизмом обновлений конфигурации, что усложняет сопровождение и поддержку системы. Разработчикам будет сложнее понять, какая логика выполняется на уровне БД.
Производительность и масштабирование: Хотя хранимые процедуры могут повышать производительность для отдельных операций, чрезмерное количество динамически создаваемых процедур или очень сложная логика в них может негативно сказаться на общем быстродействии SQL Server. Масштабирование такой архитектуры может быть более сложным, чем вынесение логики на другие серверы приложений.
Привязка к технологии БД: Жесткая привязка бизнес-логики к специфической технологии базы данных (T-SQL для SQL Server) может затруднить миграцию на другую СУБД в будущем.
Несоответствие политике 1С: Прямое изменение схемы базы данных или создание объектов вне механизмов 1С может привести к непредсказуемому поведению системы, сложностям при обновлениях платформы или конфигурации, а также проблемам с поддержкой со стороны вендора.