You’ll have noticed from the syntax diagram that there are three different types of Function.
SQL PROMPT USER FOR VALUE PDF
(a high-resolution PDF version of this diagram is available at the end of the article) The different types of function
Transact-SQL User-Defined Function Syntax – Railroad Diagram Table functions don’t support recursion at all, sadly. Scalar functions can be recursive up to 32 levels. Functions can be used in calculated fields and check constraints. This includes computed columns and CHECK constraint definitions. How Functions are executedįunctions can be used in sql expressions within batches, stored procedures, replication-filter procedures, views, triggers, other scalar or table functions, and old-style rules wherever SQL Server’s built-in scalar expressions can be used. In this article, we won’t discuss CLR functions. They just take more care and consideration. There are plenty of places in any database solution where multi-statement functions are by far the best solution to a problem, despite their restrictions, cautions and performance problems. It is, however, a mistake to condemn them altogether, because they are, in some cases, essential. When misused, multi-statement functions can cause a lot of performance problems. These, which are characterised by their BEGIN….END block, need to be used with caution. Certainly, inline table-valued functions can be used guilt-free where you’d want to use a parameterized view, but you can’t say the same of any type of multi-statement function, whether it produces a scalar value or a table. It is tempting to use them to write SQL code that conforms to good procedural practice in maintaining a single responsibility. They aren’t, however, quite like functions in a procedural language, or even the built-in system functions such as GetDate().
User-Defined Functions are an essential part of T-SQL. SQL Server User-Defined Functions - Simple Talk