Writing inline table-valued functions that need internal parameter initialisations

TLDR:

You cannot write inline TVFs (ITVFs) if you DECLARE variables that require initialisation before you use them in your TVF return SELECT statement. Here's a trick I stumbled upon where you can still do ITVFs that require internal parameter initialisations and "preprocessing". Trick: use common table expressions (CTEs) for setting params & doing pre-query logic, and then use CROSS/OUTER APPLY to use the parameters for your final query.

The longer version:

Last night, after coffee number 4 for the day, this old brain managed to [create] an inline TVFs that also required internal variables and pre-processing (things typically done with "DECLARE" statements and multiple lines). As you would know, the moment you hit a "DECLARE" statement inside your TVF, you are forced to define the resultant table and then are also forced to insert data into this "temp" table before you return that table. And this means you cannot create the TVF as an inline TVF. Not nice. Inline TVFs are preferred because they are high-performing and SQL Server is given access to the logic for index optimisation and query planner (parameter sniffing can happen because inline allows query planner to unpack the whole statement for SQL Server to do its magic).

Now look at the image of the SQL statement above. You can use a common table expression (CTE) to achieve what you would have otherwise done in DECLARE statements. The "WITH" section is where you now do the logic to set as many parameters you may require or do complex pre-processing. You then SELECT from the CTE (your parameters) and use CROSS APPLY/OUTER APPLY to do your actual query for the TVF, using your internal parameters in your CROSS/OUTER APPLY statements.

No need to create TVF "temp" table definitions and then insert into them!

Comments, corrections, violent disagreements :-) welcomed!

AK

Johannesburg

2024-09-18