Error Handling in T-SQL Scalar Function

Question

This question could easily take multiple paths, so I will hit the more specific path first. While working with SQL Server 2005, I'm trying to create a scalar function that acts as a 'TryCast' from varchar to int. Where I encounter a problem is when I add a TRY block in the function;

CREATE FUNCTION u_TryCastInt
(
   @Value as VARCHAR(MAX)
)
RETURNS Int
AS
BEGIN
   DECLARE @Output AS Int

   BEGIN TRY
      SET @Output = CONVERT(Int, @Value)
   END TRY
   BEGIN CATCH
      SET @Output = 0
   END CATCH

   RETURN @Output
END

Turns out theres all sorts of things wrong with this statement including "Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function" and "Invalid use of side-effecting or time-dependent operator in 'END TRY' within a function". I can't seem to find any examples of using try statements within a scalar function, which got me thinking, is error handling in a function is possible?

The goal here is to make a robust version of the Convert or Cast functions to allow a SELECT statement carry through depsite conversion errors. For example, take the following;

    CREATE TABLE tblTest
    (
        f1 VARCHAR(50)
    )
    GO

    INSERT INTO tblTest(f1) VALUES('1')
    INSERT INTO tblTest(f1) VALUES('2')
    INSERT INTO tblTest(f1) VALUES('3')
    INSERT INTO tblTest(f1) VALUES('f')
    INSERT INTO tblTest(f1) VALUES('5')
    INSERT INTO tblTest(f1) VALUES('1.1')

    SELECT CONVERT(int,f1) AS f1_num FROM tblTest

    DROP TABLE tblTest

It never reaches point of dropping the table because the execution gets hung on trying to convert 'f' to an integer. I want to be able to do something like this;

SELECT u_TryCastInt(f1) AS f1_num FROM tblTest

fi_num
__________
1
2
3
0
5
0

Any thoughts on this? Is there anything that exists that handles this? Also, I would like to try and expand the conversation to support SQL Server 2000 since Try blocks are not an option in that scenario.

Agrapha

For your particular use case this might help. tek-tips.com/faqs.cfm?fid=6423 – Epiphragm

Answer 1

Check if you can convert to int first, check out the IsInteger function here: IsNumeric, IsInt, IsNumber It will work on 2000 and up

Pavkovic

Answer 2

And, to answer in general: No, you can't use try-catch logic in a function. I can sort of see why - or at least it's clearly preferable to avoid it, given the huge performance penalty that would come with it.

However, I think it is odd that one also cannot RAISE an error in a function... that's something built-in functions already do. I suppose one has to get by returning NULL.

Tarragon

Answer 3

The TRY…CATCH construct cannot be used in a user-defined function in SQL 2012!

See this: http://msdn.microsoft.com/en-us/library/ms175976.aspx

When I try to use this script:

CREATE FUNCTION u_TryCastInt
(
   @Value as VARCHAR(MAX)
)
RETURNS Int
AS
BEGIN
   DECLARE @Output AS Int

   BEGIN TRY
      SET @Output = CONVERT(Int, @Value)
   END TRY
   BEGIN CATCH
      SET @Output = 0
   END CATCH

   RETURN @Output
END

I got error:

Msg 443, Level 16, State 14, Procedure u_TryCastInt, Line 10
Invalid use of a side-effecting operator 'BEGIN TRY' within a function.
Msg 443, Level 16, State 14, Procedure u_TryCastInt, Line 12
Invalid use of a side-effecting operator 'END TRY' within a function.
Msg 443, Level 16, State 14, Procedure u_TryCastInt, Line 13
Invalid use of a side-effecting operator 'BEGIN CATCH' within a function.
Msg 443, Level 16, State 14, Procedure u_TryCastInt, Line 15
Invalid use of a side-effecting operator 'END CATCH' within a function.

Plaint