Menu
I want to write a non-CLR user-defined function in SQL Server 2005. This function takes an input string and returns an output string. If the input string is invalid, then I want to indicate an error to the caller.
My first thought was to use RAISERROR to raise an exception. However, SQL Server does not allow this inside a UDF (though you can raise exceptions in CLR-based UDFs, go figure).
My last resort would be to return a NULL (or some other error-indicator value) from the function if the input value is in error. However, I don't like this option, as it:
Is there any caller-friendly way to halt a function on an error in SQL Server?
201
Handling Errors Using Do-Catch. You use a do - catch statement to handle errors by running a block of code.
In UDF, SQL Server does not provide any way for error handling. Error handling commands are not valid in UDF. Errors are handled in the caller (SP or batch) by TRY/CATCH. However for scalar function, you can return a special value to indicate an error.
In C/C++, the library function ferror() is used to check for the error in the stream. Its prototype is written as: int ferror (FILE *stream); The ferror() function checks for any error in the stream. It returns a value zero if no error has occurred and a non-zero value if there is an error.
In C++, a function can specify the exceptions that it throws using the throw keyword. The caller of this function must handle the exception in some way (either by specifying it again or catching it). 3) Grouping of Error Types: In C++, both basic types and objects can be thrown as exceptions.
It seems that SQL Server UDF's are a bit limited in this (and many other) way.
You really can't do a whole lot about it - that's (for now) just the way it is. Either you can define your UDF so that you can signal back an error condition by means of its return value (e.g. returning NULL in case of an error), or then you would almost have to resort to writing a stored procedure instead, which can have a lot more error handling and allows RAISERROR and so forth.
So either design your UDF to not require specific signaling of error conditions, or then you have to re-architect your approach to use stored procedures (which can have multiple OUTPUT parameters and thus can also return error code along with your data payload, if you need that), or managed CLR code for your UDF's.
Sorry I don't have a better idea - for now, I'm afraid, those are your options - take your pick.
Marc
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
