Custom Function Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Custom Function Problem

Author

Topic

mehere
Starting Member

3 Posts

Posted - 2005-03-31 : 14:23:01

I have a temp Table in SQL Server that is created and populated from a flat text file that we receive on a monthly basis. All fields in this temp table are defined as varchar.

From this temp table 3 other tables are populated from the date, however, several fields need to be converted to other data types along with some other string manipulation. I have the one manipulation taken care of but I can't get the data to convert to either a decimal or money. Neither CAST nor CONVERT works. Here's the function:

alter function replaceSymbol (@result varchar(20))
returns varchar(20)  -- tried decimal and money, receive errors
as
begin

--Declare @result varchar(500)
Declare @cent varchar(5)
Declare @dollar varchar(25)
Declare @xLen int
Declare @type_amount int
Declare @strDel char(1)

-- if last character symbol is below, amount needs to be negative
if right(@result, 1) = '}' or 
	right(@result, 1) = 'J' or 
	right(@result, 1) = 'K' or 
	right(@result, 1) = 'L' or 
	right(@result, 1) = 'M' or 
	right(@result, 1) = 'N' or 
	right(@result, 1) = 'O' or 
	right(@result, 1) = 'P' or 
	right(@result, 1) = 'Q' or 
	right(@result, 1) = 'R'
begin
        set @type_amount = 1
end

set @result = replace(@result,'{','0')
set @result = replace(@result,'A','1')
set @result = replace(@result,'B','2')
set @result = replace(@result,'C','3')
set @result = replace(@result,'D','4')
set @result = replace(@result,'E','5')
set @result = replace(@result,'F','6')
set @result = replace(@result,'G','7')
set @result = replace(@result,'H','8')
set @result = replace(@result,'I','9')
set @result = replace(@result,'}','0')
set @result = replace(@result,'J','1')
set @result = replace(@result,'K','2')
set @result = replace(@result,'L','3')
set @result = replace(@result,'M','4')
set @result = replace(@result,'N','5')
set @result = replace(@result,'O','6')
set @result = replace(@result,'P','7')
set @result = replace(@result,'Q','8')
set @result = replace(@result,'R','9')

set @xLen = len(@result)
set @cent = right(@result, 2)
set @dollar = left(@result, @xLen-2)
set @strDel = '.'
set @result = @dollar+@strDel+@cent

-- tried doing convert and cast, get errors

-- changing value to negative figure
if @type_amount = 1
begin
	set @result = @result*-1
end

return @result
end

Manipulation requires that I convert the last symbol in the string and add a decimal(.) before the last 2 numbers. This is then an amount that needs to be inserted into a datatype field of money in the new table.

However, when I process the function with the following, I get the following error:


Declare @test varchar(20)
set @test = '1234567R'
select dbo.replaceSymbol(@test) as test

Syntax error converting the varchar value '123456.79' to a column of data type int.

What am I missing in this? Or what else do I need to do? This is my first using SQL Server stored procedures, custom functions, etc. If I run this function without the line in red (indicated above) I get the figure: (but I need to determine if it's positive or negative)

123456.79

Any help you can provide would be greatly appreciated.

Thank you.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 14:49:47

First of all, if you need to return numeric data, set the return type of the function to numeric or int. I suspect you want something like numeric(10,2) because as you (hopefully) know integers are whole numbers only and have no decimal portion.

so, change the return type to:

numeric(10,2)

and change the final portion of your function to something like:


-- changing value to negative figure
if @type_amount = 1
	set @result = '-' + @result
return convert(numeric(10,2),@result)
end

that's it ..

- Jeff

mehere
Starting Member

3 Posts

Posted - 2005-03-31 : 16:02:15

Thank you, but I tried it several ways, all with the same outcome. However, after a few minutes of playing, it turned out to be the way I was using the function in the Stored Procedure that was causing the problem. All is fixed.

I had to change this:

if @type_amount = 1
begin
	set @result = @result*-1
end

to:

if @type_amount = 1
begin
	set @result = '-'+@result
end

And declare my variable in the stored procedure to varchar(20) then just run the function with a convert function to money and it all worked.

Thanks for your help though.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 16:06:06

again, not sure why you are returning a varchar() and then converting it when the function itself should be returning the correct datatype, but some people do like doing things the harder, more complicated way I guess ... (let's them charge a higher hourly fee I suppose!)

- Jeff

mehere
Starting Member

3 Posts

Posted - 2005-03-31 : 17:52:49

Okay, I instituted your changes but I still needed to change the variable declaration in my stored procedure to make it work.

I think when I was trying return numeric(15,2) and return decimal(15,2) my declaration within the stored procedure was still messed up. My first time with stored procedures and functions, but getting there.

Thanks again for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources