Error Handling in T-SQL Scalar Function
- by hydroparadise
Ok.. 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 funtion 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. Thanks in advance.
