Inline and multi-statement table-valued functions
Part four of a four-part series of blogs

This blog shows you how to create table-valued functions in SQL (ie functions which return a table of data!).

  1. Table-valued functions in SQL
  2. Simple (in-line) table-valued functions
  3. Multi-Statement Table-Valued Functions
  4. 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!

This blog has 0 threads Add post