tags:

views:

544

answers:

3

Why doesn't SQL Server support TRY-CATCH blocks inside UDFs?

If we're talking about scalar UDFs, which are mostly used for calculations and conversations, this block should be heavily used, but we don't have it.

Also, what workarounds do you use for this?

+1  A: 

As a work around, I would call the UDF from TRY/CATCH inside a stored procedure.

kevchadders
Thanks for the answer, but when you're writing an UDF it's usually reused by other people. Your solution works if I write both function and the caller, but you can never be sure others will call your procedure the right way.
Fedor Hajdu
true... i think ck made a good point about the potential of it being called thousands of times, so the overhead of a try/catch could be huge.
kevchadders
+1  A: 

Maybe it is because the overhead is too much - a scalar function could be called on a column as part fof a select and so be called thousands of times. If there was a reasonable overhead to allow try/catch it would slow it down horrendously.

ck
+1 good point about overheads
kevchadders
Yes, ck, but we could say the same for many other aspects of programming (cursors come to mind). I would say it's up to developer who writes the function to decide whether it's implementation and possible usage will be overhead, and code accordingly.
Fedor Hajdu
+3  A: 

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.

Simon

Pondlife