Table-Valued Function Expression (U-SQL)

  • [アーティクル]

Summary

U-SQL allows table-valued functions (TVF) to be called as primary rowset expressions even outside of a SELECT’s FROM clause. Note that the statements that make up the definition of a U-SQL Table-valued function are always inlined where they are called, while preserving the visibility rules of the context. For more information, see U-SQL Table-valued Functions.

A U-SQL TVF can either return a single rowset or multiple rowsets. If it returns multiple rowsets it can only be assigned to rowset variables and cannot be called in a FROM clause, set expressions, or OUTPUT .

Syntax

Function_Call :=                                                                                         
     Identifier '(' [Argument_List] ')'.

Argument_List :=
     Argument {',' Argument}.

Argument :=
     argument_expression  
|    'DEFAULT'.

Remarks

  • Identifier
    The identifier invokes the table-valued function (TVF) of the given name. If the function name is a fully-specified three-part name, the function in the specified database and schema will be called. If the name is a two-part name, the function will be called in the current database context and specified schema. If the name is a simple identifier, then the function will be called in the current database and schema context. If the function is not found, an error is raised.

  • Argument_List
    A function may take optional arguments for which values need to be provided. Unlike C# functions, one does not specify the argument name. Instead the values are assigned positionally to the arguments. One can either provide values using an argument expression or one can use the DEFAULT keyword to have the argument’s default value chosen.

    Any expression passed to a TVF argument will be inlined in the function’s body. This means that non-deterministic expressions are not made deterministic by U-SQL; they will still be non-deterministic if they are invoked more than once by the final query.

    Argument expressions are limited to constant-foldable expressions and references to columns and rowset variables.

Examples

The calls below are for the functions created at CREATE FUNCTION (U-SQL): Table-valued Function.

USE TestReferenceDB;

// Calling SearchLog
OUTPUT dbo.SearchLog()  
TO "/Output/ReferenceGuide/DDL/Functions/callSearchlog.csv"
USING Outputters.Csv();  


// Calling SearchLogPerRegion
@SearchLogPerRegion = dbo.SearchLogPerRegion(DEFAULT);  
OUTPUT @SearchLogPerRegion  
TO "/Output/ReferenceGuide/DDL/Functions/callSearchLogPerRegionA1.csv"
USING Outputters.Csv();  

// Calling SearchLogPerRegion and using ORDER BY with FETCH
@SearchLogPerRegion = dbo.SearchLogPerRegion(DEFAULT) ORDER BY Start DESC FETCH 1 ROWS;  
OUTPUT @SearchLogPerRegion  
TO "/Output/ReferenceGuide/DDL/Functions/callSearchLogPerRegionA2.csv"
USING Outputters.Csv();  

// Calling SearchLogPerRegion
OUTPUT dbo.SearchLogPerRegion("en-us")  
TO "/Output/ReferenceGuide/DDL/Functions/callSearchLogPerRegionB.csv"
USING Outputters.Csv();  


// Calling SearchLogPerRegionWithRemainder
(@definedRegion, @remainderRegions) = SearchLogPerRegionWithRemainder("en-us");
// First result set
OUTPUT @definedRegion
TO "/Output/ReferenceGuide/DDL/Functions/SearchLogPerRegionWithRemainderA.csv"
USING Outputters.Csv();  

// Second result set
@result = 
    SELECT * FROM @remainderRegions
    WHERE Query == "microsoft";
    
OUTPUT @result
TO "/Output/ReferenceGuide/DDL/Functions/SearchLogPerRegionWithRemainderB.csv"
USING Outputters.Csv();

See Also