UDF limitations in SQL Server
Posted by decipherinfosys on July 16, 2008
UDFs (User Defined Functions) are a great feature in every RDBMS. We have covered their usefulness before in our blog posts. Based on some of the questions that we have been getting, we thought about writing a blog post that just talks about the limitations of the UDFs in SQL Server, so here goes:
1) The TRY…CATCH block cannot be used in a UDF. Infact, other error handling modes: @@ERROR or RAISERROR are not supported either.
2) Non deterministic functions like getdate() cannot be used with a UDF. Though there are workarounds for it. And it is allowed in SQL Server 2008.
3) A UDF (any of the three variations – scalar, inline or multi-statement) cannot be used to return multiple result sets.
4) UDFs cannot call stored procedures. They can, however, call extended stored procedures.
5) UDFs cannot make use of dynamic SQL or temporary tables within the code. Table variables are allowed though.
6) Any type of SET commands are not allowed with a UDF.
Again, do remember that UDFs have a lot of use in T-SQL. The above points are just to summarize the limitations since many questions like why can’t I use TRY-CATCH in a UDF or why doesn’t usage of a temp table (though through a table variable, that is hardly a limitation) work in a UDF get addressed by the above post. All this is very well documented in BOL as well.