Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Combining variable values with result set Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 5:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 231, Visits: 1,030


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?
Post #1513310
Posted Monday, November 11, 2013 10:00 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 3:25 AM
Points: 718, Visits: 547
But you are not inserting anything in #C and @sql is also not declared.
Post #1513329
Posted Tuesday, November 12, 2013 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
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.

Post #1513443
Posted Tuesday, November 12, 2013 3:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 231, Visits: 1,030
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.
Post #1513650
Posted Wednesday, November 13, 2013 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
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.

Post #1513762
Posted Wednesday, November 13, 2013 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 110, Visits: 788
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
Post #1513830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse