Problem creating a Conversion Function

[aruba]

I am trying to convert anything over 1000000 to "A00000" and over 1100000 to "B00000" etc. The following code returns the correct string:
declare @char_parentwo varchar(9)
declare @parentwo bigint
set @parentwo = '1129356'

If @parentwo > 999999 and @parentwo < 1100000
set @char_parentwo = 'A' + substring(convert(varchar(9),@parentwo),3,5)
else
if @parentwo > 1099999 and @parentwo < 1200000
set @char_parentwo = 'B' + substring(convert(varchar(9),@parentwo),3,5)
print (@char_parentwo)

However when I try to change this to a function, it only returns the letter and not the remainder of the number. Here is the code:
create function wkctl.parentwo_convertToP3T(@parentwo bigint)
returns varchar
as
begin
declare @char_parentwo varchar(9)

If @parentwo > 999999 and @parentwo < 1100000
set @char_parentwo = 'A' + substring(convert(varchar(9),@parentwo),3,5)
else
if @parentwo > 1099999 and @parentwo < 1200000
set @char_parentwo = 'B' + substring(convert(varchar(9),@parentwo),3,5)
return (@char_parentwo)
end

Can anyone suggest what I'm doing wrong please?

 

[JamesLean]

You need to specify a length for the return value:

RETURNS varchar(20)

varchar parameters have a length of 1 if not specified.

--James

 

[aruba]

What should the syntax for this line be? Also, how would I change this to a Case statement? Thanks.

 

[JamesLean]

You already have the line in your script - you just need to add the length to the varchar:

create function wkctl.parentwo_convertToP3T(@parentwo bigint)
returns varchar(20)
as
...

--James

 

[aruba]

Thanks so much! That solved it. Can you also show me how I would change the code to a Case statement? Your help is appreciated!

 

[JamesLean]

Something like this:

create function wkctl.parentwo_convertToP3T(@parentwo bigint)
returns varchar(9)
as
begin
declare @char_parentwo varchar(9)

SET @char_parentwo = CASE
    WHEN @parentwo BETWEEN 1000000 AND 1099999 THEN 'A'
    WHEN @parentwo BETWEEN 1100000 AND 1199999 THEN 'B'
  END
  + substring(convert(varchar(9),@parentwo),3,5)

return (@char_parentwo)
end

--James

 

[aruba]

Perfect! Thanks so much