User-defined functions

Contents

User-defined functions#

Like functions in programming languages, user-defined functions (UDF) are routines that accept parameters, perform an action, and return the result of that action.

A table valued user-defined function (TVUDF) takes zero or more input parameters and returns a tabular result set.

When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. User-defined functions don’t support output parameters.

Querona supports:

  • Inline table-valued functions — custom user-defined functions whose body is a single RETURN (SELECT …), just like in SQL Server.

  • CLR table-valued functions — used to encapsulate provider calls, such as the functions generated by the REST provider (see REST).

Note

For now, Querona supports only table-valued functions — the inline and CLR forms above. Scalar user-defined functions and multi-statement table-valued functions are not yet supported. See Feature support.

Arguments#

Each argument is converted to the declared type of its parameter before the function body sees it, as SQL Server converts it: a string passed to an int parameter is read as a number, and a longer string passed to a varchar(10) parameter is cut to ten characters. A value the parameter type cannot hold fails the statement with the conversion error.

An argument can be a column of another table only when the function is called with CROSS APPLY or OUTER APPLY. From a plain join, or from a comma-separated FROM list, SQL Server refuses the column reference with error 4104, and Querona refuses it with the same error for built-in, inline and CLR functions alike. A column alias list after the call, as in dbo.f(1) AS f(x), is refused as SQL Server refuses it: error 317 for a call with a schema, error 195 for a call without one.

Note

  • An inline function called with APPLY is expanded into the query, so it runs where the APPLY runs: on the source of the left-hand table, or on the default federator. A source and federator that support neither APPLY nor LATERAL cannot run it, and the statement fails.

  • A CLR function generated by the REST provider can take a column of the left-hand table when it is called with CROSS APPLY or OUTER APPLY: Querona calls the endpoint once for every left row, one call at a time, with that row’s values as the request parameters. Equal values are not cached, so two left rows with the same value make two calls. Called with literals, variables or parameters only, the function is called once.

  • The conversion to the parameter type is an explicit one, so a value SQL Server refuses to convert implicitly - for example a datetime passed to an int parameter, refused with error 257 - is converted here.

See Feature support.

See also#