BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This blog shows you how to create table-valued functions in SQL (ie functions which return a table of data!).
- Table-valued functions in SQL
- Simple (in-line) table-valued functions
- Multi-Statement Table-Valued Functions
- Limitations of table-valued functions (this blog)
Posted by Andy Brown on 08 February 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Limitations of table-valued functions
I use table-valued functions throughout my systems, but it's as well to know of a few limitations they share with normal scalar functions. These are as follows:
|No "side-effects" allowed||Functions that you write can't insert, delete or update permanent tables.|
|Calling stored procedures is not possible||Functions can't call normal stored procedures (although they can call extended stored procedures and other functions).|
|Non-deterministic functions are barred||You can not use certain non-deterministic system functions such as RAND (a function to generate random numbers).|
|No temporary tables||You can not use temporary tables within a user-defined function.|
|Limited Error Trapping||You can not use TRY / CATCH blocks in user-defined functions.|
It's unlikely that any of the above should trouble you, but I thought I ought to include this information for the sake of completeness!