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

Я использую таблицу #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 как системное имя. 2. На самом деле, я удалил параметр @tableName как системное имя (значение: #temp было передано ему), так как он выдавал ошибку в Select and 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? Второй вопрос: как я могу сделать массив имен полей и передать его хранимой процедуре?


arrays sql-server temp
person Sushil Pugalia    schedule 16.07.2012    source источник
comment
Вы должны передать @strVal в качестве параметра для sp_executesql, а не просто объединять его в строку, которая выполняется, чтобы избежать внедрения SQL.   -  person Martin Smith    schedule 16.07.2012

Ответы (1)


arrow_upward
2
arrow_downward

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

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

person Gulli Meel    schedule 16.07.2012
comment
Пожалуйста, перейдите по следующей ссылке, где EXEC и sp_executesql очень хорошо объясняются. Полезны даже примеры: sommarskog.se/dynamic_sql.html#sp_executesqlong - person Sushil Pugalia; 19.07.2012