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