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
– Jeroen Mostert, Commented Jun 25, 2019 at 11:41
@JeroenMostert : Yes you are right. I missed it and have deleted the comment not to confuse the user. — Dheerendra
– Dheerendra, Commented Jun 25, 2019 at 11:50

Thom A · Accepted Answer · 2019-06-25 11:48:07Z

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, 2019 at 11:48

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Over a year ago

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 :-)

Pouyan Asadi Over a year ago

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.

Thom A Over a year ago

"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.

EzLo · Accepted Answer · 2019-06-25 11:43:26Z

2

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.

answered Jun 25, 2019 at 11:43

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

1 Comment

Zohar Peled Over a year ago

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.

Adam · Accepted Answer · 2019-06-25 11:49:48Z

0

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

answered Jun 25, 2019 at 11:49

Adam

2,0313 gold badges39 silver badges62 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related