2

I have a table 

DECLARE @Results TABLE(QueryIndex smallint, FieldValue nvarchar(50))

QueryIndex is a loop counter, it will get value from @QueryIndex. FieldValue will get value from another SQL String. For some reason, I need to execute SQL string dynamically. 

SET @SQL = "SELECT " + @FieldName + " FROM MyView"
            + " WHERE Condition1='" + @Value1 + "'"
            + " AND Condition2=" + CONVERT(nvarchar(12),@Value2)

Now I try to insert data into @Results

NSERT INTO @Results(QueryIndex, FieldValue)
SELECT @QueryIndex, EXEC (@SQL)

No surprise, this code won't work. Please provide me solution to insert data into my table. Any methods will be worth trying. The result in table should like this:

QueryIndex   FieldName
  1            First
  2            Second
  3            Third

Thanks.

Shinigamae
  • 854
  • 3
  • 18
  • 35

2 Answers2

3

You need to combine the retrieval of @QueryIndex with the select then you can simply

SET @SQL = 'SELECT ' + cast(@QueryIndex as varchar(16)) + ',' + @FieldName + ...
INSERT INTO @Results(QueryIndex, FieldValue)
   EXEC (@SQL)
Alex K.
  • 171,639
  • 30
  • 264
  • 288
1

you should create the structure of the containing table - before !

 CREATE TABLE #tmp  // or @tbl... doesnt really matter...
      ( 
        ...
      ) 

    INSERT INTO #tmp  -- if we use exec we  must have the pre-Structure of the table
    EXEC (@sql)

now , Do waht ever you want to do with #tmp....

Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • `#tmp` or `@tbl` may matter - `@tmp` couldn't always be the target of insert / exec (though I honestly forget when that changed). – Aaron Bertrand May 02 '12 at 11:43
  • @Royi Namir thanks for suggesting. But I think it is not solution for my case. As Alex's solution, I just want to insert 2 values at the same time. There's not much different between using temp table and using var table here :) – Shinigamae May 03 '12 at 03:29