I am writing this function and when i call it with a select statement I am getting nulls. Any ideas?

ALTER FUNCTION [dbo].[fn_deductible]

(

-- Add the parameters for the function here

@DEDUCTIBLEAMT VARCHAR(7)

)

RETURNS varchar (1)

AS

BEGIN

-- Declare the return variable here

DECLARE @DEDUCTIBLE varchar(1)

DECLARE @DEDUCTIBLETEMP varchar(7)

-- Add the T-SQL statements to compute the return value here

SET @DEDUCTIBLETEMP = CAST(@DEDUCTIBLEAMT AS NUMERIC (7,0))

set @DEDUCTIBLE = case @DEDUCTIBLETEMP

when '> 100 and <= 250' then '4'

when '> 500 and <= 750' then '6'

when '> 750 and <= 1000' then '7'

when '> 1000 and <= 2500' then '8'

when '> 2500 and <= 5000' then '9'

when '> 5000 and <= 10000' then 'A'

when '> 10000 and <= 25000' then 'B'

when '> 25000 and <= 50000' then 'C'

when '> 50000 and <= 75000' then 'D'

when '> 75000 and <= 100000' then 'E'

when '> 100000 and <= 200000' then 'F'

when '> 200000 and <= 250000' then 'G'

when '> 250000 and <= 300000' then 'H'

when '> 300000 and <= 400000' then 'I'

when '> 400000 and <= 500000' then 'J'

when '> 500000' then 'k'

end

RETURN @DEDUCTIBLE

END

>Your boundary condition

>WHEN cast(@DeductibleAmt as int) BETWEEN 400001 AND 500000 THEN 'J'

>WHEN CAST(@DEDUCTIBLEAMT AS INT) > 500000 THEN 'K'

AFAIK, and I think you have to agree, that BETWEEN is inclusive the borders?! ...

for the rest, I do agree with your suggestion to be a more complete and efficient coding...

except that you need to replace the < by <= for all the checks except the first one against 0

ALTER FUNCTION [dbo].[fn_deductible2]

(

-- Add the parameters for the function here

@DEDUCTIBLEAMT VARCHAR(7)

)

RETURNS varchar (1)

AS

BEGIN

-- Declare the return variable here

DECLARE @DEDUCTIBLECODE varchar(1)

DECLARE @DeductibleAmtINT int

SET @DeductibleAmtINT = IsNull(Cast(@DeductibleAmt

-- Add the T-SQL statements to compute the return value here

select @DeductibleCode = CASE

WHEN @DeductibleAmtInt < 0 THEN 'X'

WHEN @DeductibleAmtInt <= 50 THEN '2'

WHEN @DeductibleAmtInt <= 100 THEN '3'

WHEN @DeductibleAmtInt <= 250 THEN '4'

WHEN @DeductibleAmtInt <= 500 THEN '5'

WHEN @DeductibleAmtInt <= 750 THEN '6'

WHEN @DeductibleAmtInt <= 1000 THEN '7'

WHEN @DeductibleAmtInt <= 2500 THEN '8'

WHEN @DeductibleAmtInt <= 5000 THEN '9'

WHEN @DeductibleAmtInt <= 10000 THEN 'A'

WHEN @DeductibleAmtInt <= 25000 THEN 'B'

WHEN @DeductibleAmtInt <= 50000 THEN 'C'

WHEN @DeductibleAmtInt <= 75000 THEN 'D'

WHEN @DeductibleAmtInt <= 100000 THEN 'E'

WHEN @DeductibleAmtInt <= 200000 THEN 'F'

WHEN @DeductibleAmtInt <= 250000 THEN 'G'

WHEN @DeductibleAmtInt <= 300000 THEN 'H'

WHEN @DeductibleAmtInt <= 400000 THEN 'I'

WHEN @DeductibleAmtInt <= 500000 THEN 'J'

ELSE 'K'

END

-- Return the result of the function

RETURN @DEDUCTIBLECODE

END

I want to make a remark about the flexibility part...

I would consider creating a table like this:

tbl_DeductiableAmountCode

UpperBorder int Code char(1)

-1 X

50 2

100 3

....

2000000000 K

etc...

and your function would take that table into account, and any changes in the codings could be done by just modifying the table...

ALTER FUNCTION [dbo].[fn_deductible2]

(

-- Add the parameters for the function here

@DEDUCTIBLEAMT INT

)

RETURNS varchar (1)

AS

BEGIN

-- Declare the return variable here

DECLARE @DEDUCTIBLECODE varchar(1)

DECLARE @DeductibleAmtINT int

SET @DeductibleAmtINT = IsNull(@DeductibleAmt, -1)

-- Add the T-SQL statements to compute the return value here

SELECT top 1 @DeductibleCode = Code

FROM tbl_DeductiableAmountCode

WHERE @DeductibleAmtInt <= UpperBorder

ORDER BY UpperBorder DESC

-- Return the result of the function

RETURN @DEDUCTIBLECODE

END