SQLTeam.com | Weblogs | Forums

Call table function within dynamic script

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.

do you **really **need to do this with dynamic script if so could you explain why? Could you please also provide real usable data proper DDL and DML and list out the variables you are trying to loop through, not sure what those are?

I'd probably need to re-engineer a lot of the system, which would take Months. All the data is collected by system in a collection of tables then has to be transposed if you like into all the different scenarios from each of the tables, which represent days and schedules, of which there are about 80 altogether, which results in a huge array of views, which then need to be unioned then into a final table, and are really slow, hence the dynamic script.

Since it's already a table-valued function, can't you just pass in the value as a parameter to the function? Using a standard CROSS APPLY to call the func with the params?

Btw, the function should be an inline tvf if at all possible, because they perform vastly better than a multi-line tvf (an inline function is compiled into the main code, avoiding the overhead of actual repeated calls to it).

can't you just pass in the value as a parameter to the function?

Yes I can however I was having issues with using parameters as variables within the function's code. If I pass in the ID as a parameter, then to sue it do I need to declare a variable to use in the code itself i.e. it doesn't seem to like the variable I passed in. Not sure how to use a passed in parameter as a variable itself in code.

Also, as the dynamic code loops one of the field names changes from 1-10 at the end it, I'm thinking this is going down the dynamic script territory, or can I concatenate this into one of the function's column name so it changes with the value input from the main script?

In other words, as the main dynamic script loops through the ten schedules, I would need to say in the function for that schedule i.e. 1-10 from the original table, there is individual column for each schedule, then I need to pull that small set of data from the function for that schedule number.

Yes, you declare the variable(s) when you create the function.

Can I set the variable value as the passed in parameter or can you use the parameter straight in the SQL code? Not sure how all that works. The function will be called for each loop in the main dynamic script, each time passing in the same ID but different schedule numbers.

So for ID=1 and Sch = 1 function would return one value that I can then use to effectively reset the old schedule in the main dynamic script. It looks for certain old schedules and uses the last date of them to reset to a new schedule.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.function_name
(
    @ID varchar(30)
)
RETURNS TABLE AS
RETURN (
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 =  @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 = @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 =  @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
)
/*end of func*/
GO

You've provided almost no details, but I'm doing the best I can.

Then, to use the function in a query, something like this:

SELECT mt.col_name1, mt.col_name2, fn.col_name1, fn.col_name2, ...
FROM dbo.main_table mt
CROSS APPLY dbo.function_name(mt.ID) AS fn

@ScottPletcher Thanks, yeah I've pretty much done that. A really strange thing is happening though, if I run the script hard coded from outwith the function, the script that the function calls, or call the function in SQL with the parameter values of a certain ID (select * from dbo.fn) it returns the schedule date range I want, but when I run it via the function call within the dynamic query, it seems to be returning a different result.

Why would the same call return different results.

That does seem strange. I'd have to see the code to have any idea.

you can debug it step by step .. taking notes at every step
one line at a time .. watching input , output , = expected =

and try to find out the issue

Please Google Serach

1 Like

@harishgg1 There was no error and impossible to debug a function as it won't print out?

hi

+++++++++++
can i remote desktop to your machine and see !!!
i will able to help you quickly
+++++++++++

try to rewrite the UDF as a stored proc to debug

One of my usual ways to debug a UDF is to break it out into a script instead of a function (parameters become variables, return command becomes select), then run it that way.

you can add print/raiserror/select commands to check the status of variables, etc.,

1 Like

You stil have not provided ddl and dml and the final result you would like to see so its just all guesses.

Try this, make it loop only once and insteadnof executing the dynamic query do a print of the query variable.
Then see if the content of the dynamic query is legitinate tsql. Copy the printed out tsql and manually run it

For example you do

sql1  = N' Insert into'  + @TempTable

That translates to

Insert into#TempMyData

That wont work of course.

main_query.Col2,
main_query.Col3,
main_query.Col4 
+ ' @TableName + '

This comes out to

Select main_query.Col2
, main_query.Col3
, main_query.Col4
#TempMyData

From #TempMyData is missing.

You have a lot of other similar bugs in your code. So again before executing your dynamic query make sure it works by printing it and running it manually. Also poat ddl and dmlbwith sample data otherwise it will be just guess work

Mine too!

:+1::+1: