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.
Try-catch语句不能够在函数使用,实际上包括错误处理模式:@@ERROR 或 RAISERROR 也都不支持
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.
不确定性的函数,如getdate()不能在函数内调用;虽然有变通的方法,并且在MS2008内有支持。
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.
不能应用动态语句及临时表(局部或者全局都不行),但可以使用表变量。(所以想通过函数实现动态执行SQL语句就不用想了。)
6) Any type of SET commands are not allowed with a UDF.
任何SET命令都不允许在函数内操作。