Systems Engineering and RDBMS

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.

One Response to “UDF limitations in SQL Server”

  1. Jader said

    Good article, need more content though

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: