I'm working on a dynamic script that populates one table of data from 3 different sources, initially populated into a temp table then inserted into a final table. It used to be we had a massive chunk of views that ran and done this, but condensed it down to the dynamic script with temp tables which works much quicker and is much easier to maintain.
I've however came across an issue in that to get a certain portion of this data I need to create an inline table, join it to the main query to restrict it to the data that I want, but since the main script is dynamic I then created an inline table value function to return the table of data I want to join to.
Reason for this is the outer main query is returning all data and, within the query, is creating a weekly schedule for the dates returned, but in the 2nd query I need to restrict to only a small selection of data to create those weekly schedules.
Problem is the query in the inline table function I've created needs to also be dynamic, which I ca't do in a function, but I can in a SP. However I can't directly join to the SP values, but can with a function, although I can't use that.
I've read you can create a temp table or view from the procedure but how do you tie the parameters you are passing into the procedure to the main query, especially from an already dynamic query?
Within the function I need to have the outer query ID passed into the query function, but I can't seem to find a way to do that.
So the main script is like this:
Begin
Declare *All Variables including temp table name *
Create Table #TempMyData
(Col 1 VARCHAR(20)
Col 2 VARCHAR(20)
Col 3 VARCHAR(20)
Col 4 VARCHAR(20)
)
SET @TempTable = '#TempMyData'
WHILE
--Loop through all variables here including
-- One set of data
select @sql1 = N' Insert into' + @TempTable + ' Select main_query. Col1
, main_query.Col2
, main_query.Col3
, main_query.Col4
from ' + @TableName + '
where <all conditions> '
-- 2nd set of data restricted to small sub query effectively a UDF table returned table data that I join to
select @sql2 = N' Insert into' + @TempTable + ' Select main_query. Col1
, main_query.Col2
, main_query.Col3
, main_query.Col4
' + @TableName + '
CROSS APPLY (udf_small_data_set) as a
where main_query.col1 = a.col1
and main_query.col2 = a.col2
and main_query.col3 = a.col3
and main_query.col4 = a.col4'
--3rd set of data
select @sql3 = N' Insert into' + @TempTable + ' Select main_query. Col1
, main_query.Col2
, main_query.Col3
, main_query.Col4
' + @TableName + '
where all conditions '
--End of While loop
END
end
Function:
select *
from (
select max1.Col1, max1.Col2, max1.Col3, max1.Col4, row_number() over(partition by max1.Col3 order by max1.Col2 desc) as rn
FROM
(
(select top 1 t3.ID
, (select CAST(min(t2.Date) AS DATE) as Date
from TableName2 t2
where t2.ID = 'ST13'
and t2.Sch is null
group by t2.ID, t2.Sch) Date
, t3.Sch
, t3.UpdatedDate
from TableName t3
where t3.ID = 'ST13'
and t3.Sch is not null
group by t3.clientID, t3.Act1, t3.UpdatedDate
)
UNION
(select top 1 t3.ID
, (select CAST(min(t2.Date) AS DATE) as Date
from TableName2 t2
where t2.ID = 'ST13'
and t2.Sch is not null
group by t2.ID, t2.Sch) Date
, t3.Sch
, t3.UpdatedDate
from TableName t3
where t3.ID = 'ST13'
and t3.Sch is not null
group by t3.clientID, t3.Act1, t3.UpdatedDate
order by t3.UpdatedDate
)
) max1
) rn
where rn.rn = 1
So this function is called in main query script in query 2 however for the where t3.ID = 'ST13' section, I need the literal string to be the ID from the main query. If I hard code it and run the function it returns what I want. Obviously I can't replicate that with a dynamic function script. Only other way is a SP, but unsure how to directly join the values from that to the main query, or create a temp table with the values from the query at runtime.
Any thoughts much appreciated.
