November 10, 2008 at 10:25 am
I am using CAST to extract the numeric portion of a given string (which should have been constructed according to certain rules). However, in few cases, the users did not follow these rules and that causes a SYNTAX ERRROR during my cast. Is there a way to catch these errors (so that my UDF could handle this properly)?
November 10, 2008 at 10:27 am
Hi Micheal
Can you please explain a bit more what you are expecting to do , to catch the error that is been throwed by a udf or to correct the syntax error for case statement 🙂
November 10, 2008 at 10:29 am
It would also help if you posted the code for your UDF as well as both valid and invalid sample data for the function.
November 10, 2008 at 10:54 am
Ok, we're dealing with SKU-Code that are supposed to start with letter L followed by n digits (main-code) and then a '.' and a two-digit code (variant).
Examples: "S004711.00" or "S004712.07"
Illegal: "S00123X.Y2" or "(new) S4711.08"
I have a UDF that returns either the main-code or the variant, depending on the 2nd argument:
ALTER FUNCTION [dbo].[VarCode] (@MYARG char(100),@getvar bit)
RETURNS int AS
BEGIN
DECLARE @idx int,@res int
declare @sql varchar
if 0=charindex('.',@myarg)
set @res=0
else
if @getvar=1
begin
SET @idx= len(rtrim(@myarg))-charindex('.',rtrim(@myarg))
SET @res = cast(right(rtrim(@myarg),@idx) as int)
end
else
begin
SET @idx= charindex('.',@myarg)-1
SET @res = cast(right(left(@myarg,@idx),@idx-1) as int)
end
RETURN @res
END
When passing an illegal value to that function (which happens to contain a '.'), it creates a SYNTAX ERROR during the CASTs. I'd like to catch these and return a 0.
Thanks
Michael
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply