Неверное имя объекта #temp в динамическом SQL и массив имен полей

Question

Я использую таблицу #temp в двух отдельных хранимых процедурах. В одном случае он работает нормально, а в другом случае выдает ошибку "Неверное имя объекта #temp".

Первый случай:

SELECT SubsID,
       SubsName,
       AbbrName
INTO   #TEMP
FROM   SubsList
WHERE  CAST (SubsID AS VARCHAR(10)) LIKE '%' + @intRight + '%'
        OR SubsName LIKE '%' + @intRight + '%'

Работает нормально.

Чтобы сделать этот SQL более динамичным, я использую QUOTENAME и внес следующие изменения: 1. Я сделал тип данных @ColName, @sourceName, @intField и @txtField в качестве sysname. 2. Фактически, я удалил параметр @tableName как sysname (значение: #temp было передано ему), поскольку он выдавал ошибку в Select и Drop SQL. 3. Я передаю одно имя поля в @ColName. Это дало ошибку, когда я передал все три имени поля. 4. Я объявляю @cmd nvarchar(max) и назначаю ему SQL для выполнения.

SET @cmd = N'Select ' + QUOTENAME(@ColName) + 
           N' INTO #temp from ' + QUOTENAME(@sourceName) + 
           N' where CAST(' + QUOTENAME(@intField) + N' AS VARCHAR(10)) like ''%' + @strVal + 
           N'%'' or ' + QUOTENAME(@txtField) + ' like ''%' + @strVal + N'%''' --working
EXEC sp_executesql @cmd;

SELECT *
FROM   #temp;

DROP TABLE #temp;

Я изменил EXEC sp_executesql @cmd на EXEC(@cmd), но ошибка осталась.

Я получаю сообщение об ошибке неверного имени объекта, но если я изменю #temp на ##temp, этой ошибки не будет.

Мой первый вопрос: что может быть причиной этой ошибки в случае использования #temp? Второй вопрос: как я могу создать массив имен полей и передать его хранимой процедуре?

Answer 1

Временная таблица выходит из области видимости после выполнения exec. "Exec" или "sp_executesql" запускаются в своей области видимости. Таким образом, все, что создано, например, временные таблицы и переменные, уничтожается или выходит из области видимости, как только заканчивается выполнение.

Чтобы решить эту проблему. Создайте временную таблицу в основном коде.. затем вставьте в нее, используя dynmic sql, а затем прочитайте ее в основном коде.