FUNCTION
Translation reference for the SQL Server User Defined Functions
Last updated
Translation reference for the SQL Server User Defined Functions
Last updated
SQL Server only supports two types of User Defined Functions:
Using these UDFs types, is possible to subcategorized them into simple and complex, according to the inner logic.
Simple UDFs, matches the SQL Server syntax with Snowflake syntax. This type doesn't add any logic and goes straightforward to the result. These are usually match to Snowflake's SQL UDFs. Complex UDFs, makes extensive use of a particular statements (INSERT, DELETE, UPDATE, SET, DECLARE, etc) or control-of-flow blocks (IF...ELSE, WHILE, etc) and usually represents a mismatch or violation to Snowflake's SQL UDFs definition.
SQL Server UDFs have some limitations not present in other database engines (such as Oracle and Teradata). These limitations helps the translations by narrowing the failure scope. This means, there are specific scenarios we can expect to avoid.
Here are some of the limitations SQL Server has on UDFs
UDFs cannot be used to perform actions that modify the database state
User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target
User-defined functions cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.
For the full list, please check this link Create User-defined Functions (Database engine)