Область видимости временных таблиц и динамического SQL

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

Создать таблицу в более долгоживущей области

Пример создает #OuterWork во внешнем пакете. Динамический SQL вставляет в нее строку и создает собственную #InnerWork. После завершения #OuterWork остается доступной. Другой динамический запрос к #InnerWork получает ошибку 208, которую перехватывает TRY/CATCH.

CREATE TABLE #OuterWork (ItemId int NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'
    INSERT #OuterWork (ItemId) VALUES (@Id);
    CREATE TABLE #InnerWork (ItemId int NOT NULL);
    INSERT #InnerWork VALUES (99);
    SELECT ItemId AS VisibleInside FROM #InnerWork;',
    N'@Id int', @Id = 7;

SELECT ItemId AS VisibleOutside FROM #OuterWork;

BEGIN TRY
    EXEC sys.sp_executesql N'SELECT ItemId FROM #InnerWork;';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

DROP TABLE #OuterWork;

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

Если строки нужны последующим инструкциям, создайте таблицу с явной схемой снаружи, а динамическому пакету поручите заполнение. Контракт становится понятным: вызывающий код задает структуру, вложенная обработка создает данные, затем владелец читает и удаляет объект.

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

Разделить значения, схемы и соединения

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

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

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

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

Не добавлять скрытое совместное состояние

Замена #Work на ##Work создает глобальную временную таблицу с другими правилами видимости и жизни. Это не простое продление локальной переменной. Одновременные запросы могут столкнуться по имени или увидеть чужие данные. Для работы между запросами постоянная staging-таблица с уникальным идентификатором задания обычно понятнее.

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

Временное хранение не безгранично. Широкие строки, индексы и долгие сессии удерживают место в tempdb. Удаляйте крупные промежуточные объекты после последнего использования, особенно если процедура продолжает другую работу. Учитывайте и влияние отката транзакции на вставленные данные.

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

Техническая документация: Microsoft Learn: CREATE TABLE and temporary scope · Microsoft Learn: sp_executesql.