2017-01-18

Inline TVFs

Views are not sufficient in some conditions, to get parameterized function inside SQL queries.Adding parameters to queries makes them user defined,table valued.Latter one will be discussed as primary in this topic.Table Valued Functions(TVFs) have 2 types generally:Inline and Multi-line functions.

Inline TVFs let us to build basic functions embedded inside SELECT statements, also INSERT,UPDATE,DELETE clauses can be usable within.Setting parameters and function name creates new function where SELECT statement auto works,instead of writing each query manually.Latter one effects the performance of server profoundly,from cost aspect also has disadvantage.



Working with Inline TVFs makes us to set both function name and parameter,then SQL query statement.As we see near the parameter,data type should also specified.For the scenario of what we need to get as auto-working in every TVF, parameter must be determined according to its data type.For instance, to link any Year’s data as parameter, integer or date can be opted.

Syntax of inline TVFs


Afterward, write standard ‘returns table as return’ and SELECT statement to finish the function.Using function in a real example:


Created ‘bilge_adam’ function and ‘@par1’ as a parameter, then applied by writing 2005 as year.As views it is not taking just one data,TVFs can be used continually.
Multi-line TVFs has a little bit difference, within it possible to set table also, it allows for more complicated functions.
For broad explanation in Turkish follow the link (video).

No comments:

Post a Comment