as long as you declare a temp table before the EXEC command, any other queries would have that table in scope, and can insert into it (same for permanent tables)
so i think it's as easy as my edits below:
tstagliano (6/28/2013)
I was able to finally develop this SPROC that runs fine and returns the results but i would like to add an INSERT INTO clause that will load the values into a table that i could use for additional querying and joins.Thoughts?
DECLARE @Start_Date DATETIME = NULL
DECLARE @Part_Type_MP VARCHAR(1000) = ''
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)=''''
SET @sqlquery = N'SELECT ' +
'PLK.Part_no, ' +
'PLK.line_item_key, ' +
'PLK.unit_price ' +
'FROM ' +
'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +
'FROM Purchasing_v_Line_Item_e AS PLI ' +
'JOIN Part_v_Part_e AS P ' +
'ON p.plexus_customer_no = pli.plexus_customer_no ' +
'AND p.part_key = pli.part_key ' +
'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +
' AND (' + @q + @q + @Part_Type_MP + @q + @q +
' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +
@q + @q + @Part_Type_MP + @q + @q + ') >0))' +
'GROUP by p.part_no, pli.unit_price ' +
') AS PLK'
-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
CREATE TABLE #Results(ResultsId int identity(1,1) NOT NULL PRIMARY KEY,Part varchar(50),ActualCost money )
SET @finalQuery = N'INSERT INTO #Results (Part,ActualCost) SELECT ' +
'part_no AS Part, ' +
'unit_price AS ActualCost ' +
'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'
EXEC (@finalQuery)
Lowell