Запрос T-SQL не работает exec не в таблице

Question

Я пытаюсь построить запрос, который помещает его вывод в таблицу. Exec(@inloop_query) не знает объявленной таблицы ранее. (эта часть между ------------------ Это возможно, или я пытаюсь сделать что-то, что не работает? Пожалуйста, сообщите.

(У меня ошибка: необходимо объявить табличную переменную "@inloop_table". Уровень серьезности 15, состояние 2)

DECLARE @frame_db_name VARCHAR(max)
DECLARE @frame_db_id INT
DECLARE @frame_table TABLE (
    db_id INT , 
    names VARCHAR(max))
DECLARE @frame_count INT
DECLARE @frame_count_max INT
SET @frame_count = 1
SET @frame_count_max = 0
SELECT @frame_count_max = count (name) FROM sys.databases WHERE Name LIKE     'B%' and state_desc = 'online' 
INSERT INTO @frame_table SELECT database_id , name FROM sys.databases     WHERE Name LIKE 'B%' and state_desc = 'online' ORDER BY database_id

DECLARE @inloop_query VARCHAR(max)
DECLARE @Inloop_table TABLE (
    IL_SchemaName VARCHAR(max) , 
    IL_TableName VARCHAR(max) , 
    IL_IndexName VARCHAR(max) , 
    IL_IndexID INT , 
    IL_Fragment INT) 

IF @frame_count_max  <= 0  
    PRINT '@count_max (<=0) = ' + CAST(@frame_count_max AS VARCHAR)
ELSE 
    WHILE @frame_count <= @frame_count_max
        BEGIN
            SELECT @frame_db_name = names , @frame_db_id = db_id FROM     @frame_table WHERE db_id IN (SELECT TOP 1 db_id FROM @frame_table ORDER BY db_id) 
        PRINT '@count_max (>=0) = ' + CAST(@frame_count_max AS VARCHAR)
        PRINT '@count = ' + CAST(@frame_count AS VARCHAR(max)) 
        PRINT 'current DB name = ' + CAST(@frame_db_name AS VARCHAR(max))
        PRINT 'current DB ID = ' + CAST(@frame_db_id AS VARCHAR(max))
        ------------------------------------------------------------ 
        SET @inloop_query = '
            USE ' + CAST(@frame_db_name AS VARCHAR(max)) + 
            ' INSERT INTO @inloop_table 
                SELECT SCHEMA_NAME(o.schema_id)     AS SchemaName,
                OBJECT_NAME(a.object_id)     AS TableName,
                i.name                       AS IndexName,
                a.index_id                   AS IndexID,
                convert(tinyint,a.avg_fragmentation_in_percent) AS [Fragment]
            FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL,NULL, ''LIMITED'') AS a
                INNER JOIN sys.indexes i ON i.index_id = a.index_id
                    AND i.object_id = a.object_id 
                INNER JOIN sys.objects o ON a.object_id = o.object_id
            ORDER BY SchemaName, TableName, IndexID'
        EXEC(@inloop_query)
        ------------------------------------------------------------ 
        SET @frame_count = @frame_count + 1
        DELETE FROM @frame_table WHERE db_id IN (SELECT TOP 1 db_id FROM @frame_table ORDER BY db_id) 
    END

Answer 1

@inloop_table объявляется за пределами вашего @inloop_query; когда последний выполняется, он не имеет представления об этой переменной. Как насчет использования реальной таблицы?

/* comment this out:
DECLARE @inloop_query VARCHAR(max)
DECLARE @Inloop_table TABLE (
    IL_SchemaName VARCHAR(max) , 
    IL_TableName VARCHAR(max) , 
    IL_IndexName VARCHAR(max) , 
    IL_IndexID INT , 
    IL_Fragment INT)
*/
-- Create an auxiliary table
CREATE TABLE InLoop_Table (
        IL_SchemaName VARCHAR(max) , 
    IL_TableName VARCHAR(max) , 
    IL_IndexName VARCHAR(max) , 
    IL_IndexID INT , 
    IL_Fragment INT
);
-- ... And use this table in your dynamic sql:
SET @inloop_query = '
        USE ' + CAST(@frame_db_name AS VARCHAR(max)) + 
        ' INSERT INTO InLoop_Table ...

-- Finally, clean up:
DROP TABLE InLoop_Table;

Answer 2

Область действия табличной переменной зависит от пакета, поэтому, поскольку ваш динамический sql выполняется как новый пакет, он выходит из области видимости и не распознается. Конечно, вы можете объявить это в вашем динамическом sql, но это будет довольно бессмысленно, так как вы не сможете получить к нему доступ позже. У вас есть два достойных выбора:

Вы можете поместить вставку за пределы SQL, например

DECLARE @inloop_query NVARCHAR(MAX) = 'USE Master; SELECT 1, 2, 3;';
DECLARE @inloop_table TABLE (A INT, B INT, C INT);

INSERT @inloop_table
EXEC(@inloop_query);

SELECT * FROM @inloop_table;

Или вы можете использовать временную таблицу, а не переменную таблицы. Временная таблица имеет область сеанса, поэтому все еще распознается с EXEC():

CREATE TABLE #inloop_table (A INT, B INT, C INT);
DECLARE @inloop_query NVARCHAR(MAX) = 'USE Master; INSERT #inloop_table SELECT 1, 2, 3;';

EXEC(@inloop_query);

SELECT * FROM #inloop_table;

Я также рекомендовал бы использовать правильно объявленный курсор, а не WHILE цикл итерации по табличной переменной. Ключевой аспект здесь правильно определен. Часто люди просто используют DECLARE .. CURSOR FOR SELECT.. и параметры по умолчанию намного медленнее и занимают больше памяти, чем если вы скажете курсору, что не будете делать обновления, не будете двигаться назад и т. д.

DECLARE DBCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT  database_id , name 
FROM sys.databases     
WHERE Name LIKE 'B%' and state_desc = 'online' 
ORDER BY database_id;

OPEN DBCursor;
FETCH NEXT FROM DBCursor INTO @frame_db_id, @frame_db_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- DO WHATEVER YOU NEED WITH EACH DB

    FETCH NEXT FROM DBCursor INTO @frame_db_id, @frame_db_name;
END

CLOSE DBCursor;
DEALLOCATE DBCursor;

Последний комментарий: я всегда совершенствуюсь sp_executesql над EXEC() и эта статья в значительной степени объясняет, почему, в данном случае это не имеет большого значения, но это стоит отметить.