November 11, 2013 at 5:57 pm
I have a bunch of variable values that i need to combine with the result set of dynamic sql and store/insert them in a table. Currently what i am doing is storing the dynamic result set in a temp table and combine there after but it is very inefficient and unnecessary I/O overhead. Any suggestions?
DECLARE @a NVARCHAR(20)='black'
DECLARE @b-2 NVARCHAR(20)='Orange'
insert into #C
EXECUTE sp_executesql @Sql
SELECT @a, C.colA,@B,C.colB FROM #C? -- this is inefficient? Any better ideas?
November 11, 2013 at 10:00 pm
But you are not inserting anything in #C and @sql is also not declared.
November 12, 2013 at 6:57 am
Why not put the values of the variables in the dynamic sql @sql before execution
Far away is close at hand in the images of elsewhere.
Anon.
November 12, 2013 at 3:43 pm
David Burrows (11/12/2013)
Why not put the values of the variables in the dynamic sql @sql before execution
The dynamic sql is pre-composed.
November 13, 2013 at 2:58 am
peacesells (11/12/2013)
The dynamic sql is pre-composed.
Then I think you are stuck with your original query.
IIRC There have been some posts suggesting using OPENROWSET with dynamic sql to avoid the use of the temp table, in which case you could wrap the @sql with an OPENROWSET query to avoid the temp table but not sure if performance will be better.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply