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)
This blog is part of our SQL tutorial. If you're looking for a classroom course, have a look at our SQL classroom-based training.
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:
| Limitation | Notes |
|---|---|
| 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!
- Table-valued functions in SQL
- Simple (in-line) table-valued functions
- Multi-Statement Table-Valued Functions
- Limitations of table-valued functions (this blog)