Почему временная таблица SQL не видна в цикле EXECUTE и как это исправить в 1С 7.7?

← На главную

При написании прямых SQL-запросов к базам данных из 1С:Предприятия 7.7 разработчики часто сталкиваются с необходимостью динамического построения запросов. Одним из популярных сценариев является перебор нескольких информационных баз (например, архивов за разные годы) в цикле с последующим сбором информации во временную таблицу. Однако при попытке реализовать этот алгоритм стандартным способом через инструкцию EXECUTE СУБД возвращает ошибку "Msg 208, Level 16, State 0: Invalid object name '#last_buy_temp'".

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

Выясняем причину: почему падает динамический SQL?

Рассмотрим ситуацию, в которой оказался автор темы. В цикле While поочередно формируются и выполняются строки динамического SQL. На первой итерации создается локальная временная таблица #last_buy_temp:

when 2 then 'create table #last_buy_temp (CODE char(9), Last_buy_date datetime)'

На следующих шагах цикла выполняются команды наполнения этой таблицы (INSERT INTO #last_buy_temp SELECT...), но система падает с ошибкой, утверждая, что объект не существует. Проанализируем, почему так происходит с точки зрения архитектуры MS SQL Server.

Когда вы вызываете команду EXECUTE('строка_запроса') или системную процедуру sp_executesql, СУБД создает отдельный, изолированный вложенный контекст выполнения (child scope). С этим контекстом связаны определенные правила:

Локальная временная таблица (имя которой начинается с одного символа решетки #), созданная внутри динамического SQL, принадлежит исключительно этому вложенному контексту.
Как только конкретный вызов EXECUTE завершает свою работу, порожденный им контекст полностью уничтожается, а все созданные внутри него локальные временные таблицы автоматически удаляются (происходит неявный DROP TABLE).
Следующий вызов EXECUTE в новой итерации цикла запускается в абсолютно новом, независимом контексте, в котором таблицы #last_buy_temp физически больше не существует.

Попытки объединить создание таблицы и первую вставку данных в один запрос проходят успешно, но последующие обращения из новых вызовов EXECUTE неизбежно приводят к ошибке.

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

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

Это наиболее элегантный и безопасный подход, который часто упускают из виду разработчики. В СУБД MS SQL Server область видимости локальных временных таблиц работает по принципу сверху вниз (от родителя к потомку). Это означает, что **вложенный контекст динамического запроса видит временные таблицы, созданные в основном (родительском) скрипте**.

Разберем этот метод по шагам. Вместо того чтобы пытаться создать таблицу внутри динамического блока EXECUTE, мы должны объявить ее один раз в основном теле нашего SQL-скрипта еще до начала цикла:


-- Шаг 1. Создаем локальную временную таблицу в родительском контексте
IF OBJECT_ID('tempdb..#last_buy_temp') IS NOT NULL 
    DROP TABLE #last_buy_temp;

CREATE TABLE #last_buy_temp (CODE char(9), Last_buy_date datetime);

-- Шаг 2. Запускаем наш цикл по базам данных
DECLARE @countBZ int, @cou int, @zaprosText Char(7999);
SET @countBZ = 10;
SET @cou = 3; -- Начинаем сразу с шагов наполнения данных

WHILE @cou <= @countBZ 
BEGIN
    -- Формируем динамический текст запроса (только INSERT INTO)
    SET @zaprosText = (CASE @cou 
        WHEN 3 THEN 'USE rozn_2019; INSERT INTO #last_buy_temp SELECT SC46.CODE, SC46.SP3075 FROM SC46 WHERE ...'
        WHEN 4 THEN 'USE rozn_2015; INSERT INTO #last_buy_temp SELECT СпрКлн.CODE, max(...) FROM RA4580 ...'
        -- и так далее...
    END);

    -- Выполняем динамический SQL. Он без проблем запишет данные в #last_buy_temp, 
    -- так как этот child-контекст наследует видимость таблицы родителя!
    EXECUTE(@zaprosText); 

    SET @cou = @cou + 1;
END;

-- Шаг 3. Читаем итоговый результат
SELECT * FROM #last_buy_temp;

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

Решение 2. Использование глобальных временных таблиц (##) с уникальными именами

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

Глобальные временные таблицы видны всем сессиям и контекстам СУБД до тех пор, пока создавшее их подключение остается активным. Однако здесь кроется серьезная опасность: если два пользователя одновременно запустят этот отчет в 1С 7.7, они будут использовать одну и ту же таблицу ##last_buy_temp, что приведет к повреждению данных или ошибке совместного доступа.

Чтобы избежать этой проблемы, проанализируем метод генерации динамических имен таблиц. Мы можем добавлять к имени глобальной таблицы уникальный идентификатор процесса СУБД — @@SPID (Server Process ID), либо генерировать GUID на стороне 1С и передавать его в SQL:


-- Пример генерации динамического имени на основе @@SPID в T-SQL
DECLARE @dynTableName varchar(100), @sqlDrop varchar(max), @sqlCreate varchar(max);
SET @dynTableName = '##last_buy_temp_' + CAST(@@SPID as varchar(10));

-- Формируем DROP и CREATE с динамическим именем таблицы
SET @sqlDrop = 'IF OBJECT_ID(''tempdb..' + @dynTableName + ''') IS NOT NULL DROP TABLE ' + @dynTableName;
SET @sqlCreate = 'CREATE TABLE ' + @dynTableName + ' (CODE char(9), Last_buy_date datetime)';

EXECUTE(@sqlDrop);
EXECUTE(@sqlCreate);

После этого во всех итерациях цикла вам придется динамически конструировать текст запроса, подставляя туда полученное имя таблицы @dynTableName. Это рабочий, но избыточно сложный в реализации метод, требующий большого количества операций конкатенации строк.

Решение 3. Отказ от циклов в пользу динамического UNION ALL

Посмотрим на задачу с архитектурной точки зрения. Выполнение команд переключения контекста базы данных (USE rozn_2015, USE rozn_2016 и так далее) внутри СУБД в рамках цикла — крайне ресурсоемкая операция. СУБД вынуждена постоянно перестраивать контекст безопасности и кэши планов выполнения.

Гораздо эффективнее полностью отказаться от циклов While на стороне SQL-сервера и динамически сформировать из 1С один общий запрос с объединением результатов через UNION ALL, используя полные четырехчастные имена таблиц (ИмяБазы.Схема.ИмяТаблицы). Проанализируем, как будет выглядеть такой запрос:


-- Формируем один монолитный запрос без переключения баз через USE
INSERT INTO #last_buy_temp
SELECT CODE, SP3075 as Last_buy_date FROM rozn_2019.dbo.SC46 WHERE Year(SP3075) >= 2015
UNION ALL
SELECT СпрКлн.CODE, max(convert(datetime, LEFT(РегДск.DATE_TIME_IDDOC, 8), 102)) FROM rozn_2015.dbo.RA4580 as РегДск 
    INNER JOIN rozn_2015.dbo.SC46 as СпрКлн ON СпрКлн.ID = РегДск.Клиент WHERE ... GROUP BY CODE
UNION ALL
SELECT СпрКлн.CODE, max(convert(datetime, LEFT(РегДск.DATE_TIME_IDDOC, 8), 102)) FROM rozn_2016.dbo.RA4580 as РегДск 
    INNER JOIN rozn_2016.dbo.SC46 as СпрКлн ON СпрКлн.ID = РегДск.Клиент WHERE ... GROUP BY CODE
-- и так далее для каждого года истории...

Преимущества такого подхода очевидны:

Запрос выполняется за один проход, оптимизатор MS SQL Server строит единый эффективный план выполнения.
Вам вообще не нужны циклы WHILE, переменные-счетчики и динамический EXECUTE на стороне СУБД.
Текст запроса легко и наглядно конструируется в коде 1С 7.7 обычным перебором нужных баз.

Важные нюансы для разработчиков на 1С 7.7

При интеграции прямых SQL-запросов в среду 1С:Предприятия 7.7 необходимо учитывать специфику работы библиотек доступа к данным (ODBC, ADO):

Обязательно используйте директиву SET NOCOUNT ON. По умолчанию SQL Server после каждой операции манипуляции данными (например, INSERT) возвращает клиенту сообщение о количестве затронутых строк. Драйверы ADO/ODBC в 1С 7.7 могут ошибочно интерпретировать эти сообщения как закрытый набор записей. Из-за этого при чтении данных вы можете получить ошибку "Recordset is closed". Пишите SET NOCOUNT ON в самом начале вашего SQL-скрипта.
Избегайте табличных переменных. Переменные типа DECLARE @temp TABLE обладают еще более жесткими границами видимости, чем локальные временные таблицы #. К ним физически невозможно обратиться из динамического SQL (изнутри EXECUTE), даже если они объявлены в том же родительском пакете.
Задействуйте возможности внешней компоненты 1С++. Если вы используете класс ODBCRecordset, то встроенный метапарсер автоматически преобразует конструкции вида $Справочник.Клиенты в физические имена таблиц СУБД (например, SC46), что значительно упрощает разработку и поддержку кода.

Подводя итог: если вам требуется быстрое и надежное решение проблемы "исчезающих" таблиц, воспользуйтесь Способом 1 и создайте локальную временную таблицу # до запуска динамического SQL. Если же вы хотите получить максимальную производительность — перепишите алгоритм на Способ 3, объединив все выборки через UNION ALL в рамках одного запроса.