Динамический SQL: OPENROWSET с @param и INSERT в @Table

Я пытаюсь вставить в параметр @table, используя OPENROWSET с @param в цикле. Я адаптировал код из этого ответа, который демонстрирует использование динамического SQL для вставки параметра.

Код:

DECLARE @BuildTimes TABLE (
    BuildTableName VARCHAR(max) NULL
    ,BuildDate DATE NULL
)

DECLARE @days INT = 0;
DECLARE @startDate DATE = GETDATE();
DECLARE @buildDate DATE = GETDATE();
DECLARE @sql nvarchar(max);

WHILE (@days <=30)
BEGIN
    SET @buildDate = DATEADD(day, -1*@days, @startDate);
    SET @sql='INSERT INTO @BuildTimes
        SELECT * 
        FROM OPENROWSET(
                       ''SQLNCLI'',
                       ''SERVER=localhost;Trusted_Connection=yes;'',
                       ''EXEC [LOG].[BuildTimes] @buildDate = ''''' + CAST(@buildDate AS VARCHAR) +''''''')'

    PRINT @sql
    EXEC(@sql)
    SET @days = @days + 1
END

SELECT * FROM @BuildTimes

Ошибка:

Msg 1087, Level 15, State 2, Line 9
Must declare the table variable "@BuildTimes"

Я пытался запустить OPENROWSET как нединамический SQL без параметра, и все работает правильно. Что я делаю не так?


sql-server tsql openrowset dynamic-sql sql-server-2012
person Adam    schedule 25.06.2019    source источник
comment
Переменные недоступны во внутренней области EXEC. Это включает в себя табличные переменные. Это сработает, если вы используете временную таблицу (или переместите INSERT из внутреннего запроса и используйте INSERT .. EXEC).   -  person Jeroen Mostert    schedule 25.06.2019
comment
@JeroenMostert Да, ты прав. Я пропустил это и удалил комментарий, чтобы не путать пользователя.   -  person Dheerendra    schedule 25.06.2019

Ответы (3)


arrow_upward
1
arrow_downward

Зачем вообще использовать динамический SQL, а не просто использовать INSERT INTO?

DECLARE @BuildTimes table (BuildTableName varchar(MAX) NULL,
                           BuildDate date NULL);

DECLARE @days int = 0;
DECLARE @startDate date = GETDATE();
DECLARE @buildDate date = GETDATE();

WHILE (@days <= 30)
BEGIN

    SET @buildDate = DATEADD(day, -1*@days, @startDate);    
    INSERT INTO @BuildTimes (BuildTableName,
                             BuildDate)
    EXEC log.BuildTimes @buildDate;

    SET @days = @days + 1;

END;

SELECT BuildTableName,
       BuildDate
FROM @BuildTimes;
person Larnu    schedule 25.06.2019
comment
Спасибо. Я нашел то же решение, но вы опередили меня в посте на 1 минуту, поэтому я отмечу ваш ответ как правильный и удалю свой :-) - person Adam; 25.06.2019

arrow_upward
2
arrow_downward

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

Решение состоит в том, чтобы использовать временную таблицу вместо переменной:

IF OBJECT_ID('tempdb..#BuildTimes') IS NOT NULL
    DROP TABLE #BuildTimes

CREATE TABLE #BuildTimes (
    BuildTableName VARCHAR(max) NULL
    ,BuildDate DATE NULL
)

DECLARE @days INT = 0;
DECLARE @startDate DATE = GETDATE();
DECLARE @buildDate DATE = GETDATE();
DECLARE @sql nvarchar(max);

WHILE (@days <=30)
BEGIN
    SET @buildDate = DATEADD(day, -1*@days, @startDate);
    SET @sql='INSERT INTO #BuildTimes
        SELECT * 
        FROM OPENROWSET(
                       ''SQLNCLI'',
                       ''SERVER=localhost;Trusted_Connection=yes;'',
                       ''EXEC [LOG].[BuildTimes] @buildDate = ''''' + CAST(@buildDate AS VARCHAR) +''''''')'

    PRINT @sql
    EXEC(@sql)
    SET @days = @days + 1
END

SELECT * FROM #BuildTimes

Таблицу можно прочитать за пределами EXEC, поскольку она была создана снаружи, и она доступна внутри, поскольку остается в том же сеансе.

В качестве примечания избегайте использования * всякий раз, когда вы ожидаете известный набор столбцов, таким образом, если новый столбец будет добавлен в базовые таблицы из SELECT, вы INSERT не сломаетесь.

person EzLo    schedule 25.06.2019
comment
И еще одно примечание: избегайте использования CAST(@buildDate AS VARCHAR) - во-первых, потому что вы всегда должны указывать длину при работе с varchar (или любым типом, у которого есть свойство длины, в этом отношении), а во-вторых, потому что разные логины могут иметь разные настройки формата даты, и не все из них действительный. Используйте convert со 126 в качестве параметра стиля, чтобы обеспечить правильное преобразование в актуальное состояние. - person Zohar Peled; 25.06.2019

arrow_upward
0
arrow_downward

Я обнаружил, что для этого сценария мне вообще не нужен Dynamic SQL/OPENROWSET. Следующий код дает желаемый результат:

DECLARE @BuildTimes TABLE (
    BaseTableName VARCHAR(max) NULL
    ,BuildDate DATE NULL
    ,StartDateTime DATETIME NULL
    ,FinishDateTime DATETIME NULL
    ,TimeTakenMinutes BIGINT NULL
)

DECLARE @days INT = 0;
DECLARE @startDate DATE = GETDATE();
DECLARE @thisBuildDate DATE = GETDATE();

WHILE (@days <=30)
BEGIN
    SET @thisBuildDate = DATEADD(day, -1*@days, @startDate);
    PRINT @thisBuildDate

    INSERT INTO @BuildTimes
    EXEC [LOG].[BuildTimes] @buildDate = @thisBuildDate
    SET @days = @days + 1
END

SELECT * FROM @BuildTimes
GO
person Adam    schedule 25.06.2019