Error Handling in User Defined Functions

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).

  • How to return the miliseconds of a GetDate() function of T-SQL?
  • Unique index or unique key?
  • how to write a single sql query based on combobox selection?
  • Combine multiple rows in a single row by descending order SQL
  • SQL conundrum, how to select latest date for part, but only 1 row per part (unique)
  • SQL: extract date from string
  • 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:

    1. Doesn’t provide any useful information to the caller
    2. Doesn’t allow me to return a NULL in response to valid input (since it’s used as an error code).

    Is there any caller-friendly way to halt a function on an error in SQL Server?

  • sql server with different database of different time zone. how to get getdatetime for all database
  • Read value from a complex XML structure using SQL Server
  • Stored procedure using sp_send_dbmail to send emails to multiple recipients queried from database
  • Return the names of the categories which are of N’th level in the hierarchy (categories with parentId -1 are at 1st level)
  • Can I create a One-Time-Use Function in a Script or Stored Procedure?
  • Primary Key Sorting
  • 2 Solutions collect form web for “Error Handling in User Defined Functions”

    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

    There’s a possible solution given in an answer to a duplicate question here, based on this idea:

    return cast('Error message here.' as int);
    

    Which throws something like this:

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'Error message here.' to data type int.
    

    It works OK for scalar-valued UDFs, but not for table-valued ones.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.