Combining variable values with result set

  • 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 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?

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

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

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

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

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

    SELECT * FROM (@SQL) qry

    CROSS JOIN (SELECT @a a, @B b) x

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

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