Combining variable values with result set

Question

Post reply

Combining variable values with result set

peacesells

SSCrazy

Points: 2882
More actions
November 11, 2013 at 5:57 pm

#286136

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 1

But you are not inserting anything in #C and @sql is also not declared.

David Burrows SSC Guru Points: 65143 More actions · Answer 2

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.

peacesells SSCrazy Points: 2882 More actions · Answer 3

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.

David Burrows SSC Guru Points: 65143 More actions · Answer 4

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.

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 5

Can you add a cross join to the dynamic sql? Or maybe this:

SELECT * FROM (@SQL) qry

CROSS JOIN (SELECT @a a, @b-2 b) x