Error converting varchar to integer

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)

Error converting varchar to integer

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2011-03-06 : 17:19:43

I am having a problem on a stored procedure that has worked famously for months. I was getting an error converting varchar to numeric (8114) I solved this by using the trim function to remove a trailing space on a string. Now I am getting a new error, converting varchar to integer. My problem is that I don't declare the field I am updating as an integer, I CAST it as a decimal. Here is the stored procedure that I use to create the base table:

code/
CREATE procedure usp_tmiinva2

as

SELECT
TAG#
, dbo.FRINFM.GRADE
, Cast(Gauge as varchar) as Gauge
, Width
, Weight
, CAST(Weight/2000.00 AS Decimal(6,2)) AS Tons
, Status
, CAST([Cost-Cwt] AS DECIMAL(6,2)) AS [Cost-Cwt]
, dbo.tblGrades.Type AS GaugeType
, SPACE(2) AS TYPE
, CAST(0.00 AS decimal(6,4)) AS DecIn
, CAST(0.00 AS decimal(6,4)) AS DecOut
, CAST(0.00 AS decimal(6,4)) AS DecAvg
, SPACE(8) AS GaugeFinal
, CAST(Weight*[Cost-Cwt]/100 AS MONEY) AS InvValue

INTO dbo.TMIINVa
FROM dbo.FRINFM
JOIN dbo.tblGrades ON dbo.FRINFM.GRADE = dbo.tblGrades.Grade
WHERE
[status] IN (3, 4, 5, 6)
union all
SELECT
TAG#
, dbo.FRINHISTFM.GRADE
, Cast(Gauge as varchar) as Gauge
, Width
, Weight
, CAST(Weight/2000.00 AS Decimal(6,2)) AS Tons
, Status
, CAST([Cost-Cwt] AS DECIMAL(6,2)) AS [Cost-Cwt]
, dbo.tblGrades.Type AS GaugeType
, SPACE(2) AS TYPE
, CAST(0.00 AS decimal(6,4)) AS DecIn
, CAST(0.00 AS decimal(6,4)) AS DecOut
, CAST(0.00 AS decimal(6,4)) AS DecAvg
, SPACE(6) AS GaugeFinal
, CAST(Weight*[Cost-Cwt]/100 AS MONEY) AS InvValue
from dbo.FRINHISTFM
JOIN dbo.tblGrades ON dbo.FRINHISTFM.GRADE = dbo.tblGrades.Grade
WHERE
[status] IN (3, 4, 5, 6)
GO
code/

What I am doing from this point is taking a field called Gauge and parsing it so that I can separate a string like .121/.122 into two fields DecIn and DecOut. I use the mid function to find the slash then return what is left or right. The code is here:

code/

CREATE procedure usp_tmiinva4

as

update dbo.tmiinva
set DecIn =
CASE

WHEN CHARINDEX('/',Gauge)>0 THEN SUBSTRING(Gauge,1,(charindex('/',Gauge)-1))
WHEN CHARINDEX('-',Gauge)>0 THEN SUBSTRING(Gauge,1,(CHARINDEX('-',Gauge)-1))
WHEN CHARINDEX('MIN',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('MIN',Gauge)-1)))
WHEN CHARINDEX('MAX',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('MAX',Gauge)-1)))
WHEN CHARINDEX('NOM',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('NOM',Gauge)-1)))
WHEN CHARINDEX('ACT',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('ACT',Gauge)-1)))
else 0
end
update dbo.tmiinva
set DecOut =
CASE

WHEN CHARINDEX('/',Gauge)>0 THEN SUBSTRING(Gauge,(charindex('/',Gauge)+1),99)
WHEN CHARINDEX('-',Gauge)>0 THEN SUBSTRING(Gauge,(charindex('-',Gauge)+1),99)
WHEN CHARINDEX('MIN',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('MIN',Gauge)-1)))
WHEN CHARINDEX('MAX',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('MAX',Gauge)-1)))
WHEN CHARINDEX('NOM',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('NOM',Gauge)-1)))
WHEN CHARINDEX('ACT',Gauge)>0 THEN RTRIM(SUBSTRING(Gauge,1,(CHARINDEX('ACT',Gauge)-1)))
else 0

end
GO
code/

I added the RTRIM to the substring to get rid of a situation where the Gauge field contains .121 NOM. That would return .121space which would cause an error.

I am really struggling on this one since it has been working and I don't know of anything that has been added.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-07 : 04:29:44

do you get the error message by executing which SP? usp_tmiinva4 or usp_tmiinva2. Also, copy paste the complete error message.

Cheers
MIK

vavs
Starting Member

24 Posts

Posted - 2011-03-07 : 07:06:20

Whatever was causing the problem is gone now and the stored procedure is working again. Thanks for looking at it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources