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

Question

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?

Variables are not available in the inner scope of an `EXEC`. That includes table variables. It would work if you used a temp table (or move the `INSERT` out of the inner query and use `INSERT .. EXEC`). — Jeroen Mostert, Jun 25 '19 at 11:41
@JeroenMostert : Yes you are right. I missed it and have deleted the comment not to confuse the user. — Dheerendra, Jun 25 '19 at 11:50

score 2 · Answer 1 · answered Jun 25 '19 at 11:43

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.

And another note: Avoid using `CAST(@buildDate AS VARCHAR)` - first because you should always specify length when dealing with `varchar` (or any type that has a length property, for that matter) and second because different logins might have different date format settings and not all of them are valid. Use `convert` with 126 as the style parameter to ensure proper conversion back to date. — Zohar Peled, Jun 25 '19 at 11:52

score 2 · Accepted Answer · answered Jun 25 '19 at 11:48

2

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;

answered Jun 25 '19 at 11:48

Thom A

88,727
11
45
75

1

Thanks. I found the same solution, but you beat me to it on the post by 1min, so I'll mark yours as the correct answer and delete mine :-) – Adam Jun 25 '19 at 11:50
this is correct answer when you know your desire columns should be return from SP. but sometimes you don't have any idea what columns should be return. – Pouyan Asadi May 25 '22 at 13:46
*"but sometimes you don't have any idea what columns should be return."* then find out first, @PouyanAsadi . That's a solvable problem. You can easily find out what will be returned from a statement with `sys.dm_exec_describe_first_result_set`. – Thom A May 25 '22 at 13:52

score 0 · Answer 3 · answered Jun 25 '19 at 11:49

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

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

3 Answers3