Functions do not allow for Try-Catch blocks.
Function do not handle traditional error trapping very well.
My interest was in creating a function that would take care of number formatting for me. I wanted to restrict to two decimal places without embedding the code in every view I might create.
After a clear error on NOT being able to use a Try-Catch block in a function I used the following error trapping technique:
Code:
CREATE FUNCTION [dbo].[FORMATNUMBER_2DEC]
(
@Value varchar(100)
)
RETURNS varchar(25)
AS
BEGIN
Declare @Formatted varchar(100)
Declare @err int
Set @Formatted = cast(@Value as decimal(38,2))
SELECT @err = @@error IF @err <> 0 Set @Formatted = @Value
RETURN @Formatted
END
This was GREAT! At least until I passed it a string value. Then I got an error returned rather than the Value passed in.
I then modified my code to the below sample and this works like a charm!
Code:
CREATE FUNCTION [dbo].[FORMATNUMBER_2DEC]
(
@Value varchar(100)
)
RETURNS varchar(25)
AS
BEGIN
Declare @Formatted varchar(100)
If IsNumeric(@Value) = 1
begin
Set @Formatted = cast(@Value as decimal(38,2))
end
Else
begin
Set @Formatted = @Value
end
RETURN @Formatted
END
