Regarding user define function??

Question

Why we can not use Try-Catch block within Sql Server user define function.Please explain it. Thanks

Govinda Rajulu Yemineni · Answer

Hi UDFs in MSSQL are not allowed to have side effects which BOL defines as changing the database state. That's a rather vague description but MSSQL apparently considers errors to change the database state - this UDF doesn't compile: create function dbo . foo () returns int as begin raiserror ( 'Foo'  16  1 ) return 1 end go The error message is: Msg 443 Level 16 State 14 Procedure foo Line 5 Invalid use of a side-effecting operator 'RAISERROR' within a function. If raising an error is considered to change the database state then trapping and handling one presumably is too. Which is not much of an explanation I admit. In practical terms though it's often best to let the caller decide how to handle errors anyway. Say you write a function like this: create function dbo . divide (@ x int  @ y int ) returns float as begin return @ x / cast (@ y as float ) end How would you handle the case where an application passes zero for @y? If you catch the divide by zero exception what are you going to do next? What value can you return from the function that makes sense to the caller bearing in mind that you may not even know which application is calling your function anyway? You might think of returning NULL but would the application developers using your function agree? Do all their applications consider a divide by zero error to have the same impact or importance? Not to mention that NULLs in certain places can completely change the results of a query perhaps in ways that the application developer doesn't want at all. If you're the only developer maybe it isn't an issue but with more people it quickly becomes one.

Upendra Pratap Shahi · Answer

Hello Shyam Narayan  SQL Server doesn't support Try-Catch block in User defined functions(UDF) while this support in Stored procedure. This is one limitation in SQL Server UDF. To do this as per below: You can call UDF in a stored procedure with in try-catch block with query You need to validate data(ISNULL Blank value..) before doing any operation inside UDF

Manas Mohapatra · Answer

Hi SqlServer doesn't support Try-Catch block in User defined functions(UDF). This is one limitation in sqlserver UDF. The work around is : You can call UDF from a stored procedure with try-catch block You need to validate data(ISNULL Blank value..) before doing any operation inside UDF