CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012dynamic-sqlopenrowset
Adam
Adam

Reputation: 2031

Dynamic SQL: OPENROWSET with @param and INSERT into @Table

I'm trying to insert into a @table parameter using OPENROWSET with a @param in a loop. I've adapted code from: This Answer, which demonstrates using Dynamic SQL to insert the parameter.

Code:

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

Error:

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

I've tried running the OPENROWSET as non-dynamic SQL without the param and it all works properly. What am I doing wrong?

Upvotes: 0

Views: 1653

Answers (3)

Adam
Adam

Reputation: 2031

I found that I didn't need Dynamic SQL / OPENROWSET at all for this scenario. The following code produces the desired result:

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

Upvotes: 0

Thom A
Thom A

Reputation: 95924

Why use dynamic SQL at all and not just use 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;

Upvotes: 2

EzLo
EzLo

Reputation: 14209

Your variable table @BuildTimes isn't accessible inside Dynamic SQL. Even if you declare it and load it with dynamic SQL, you won't be able to read the results outside of the dynamic scope.

A solution is to use a temporary table instead of a variable one:

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

The table can be read outside the EXEC because it was created outside, and it's accessible inside because it remains on the same session.

As a side note, avoid using * whenever you expect a known set of columns, that way if a new column is added on the underlying tables from the SELECT, you INSERT won't break.

Upvotes: 2

Related Questions