Error converting varchar to numeric

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

General SQL Server Forums

New to SQL Server Programming

Error converting varchar to numeric

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2010-10-07 : 10:23:23

I am running a stored procedure on our sales order data. I run a similar stored procedure without error.

Here is the procedure:
/*
CREATE procedure usp_kdsls4
as
update KDSales
set DecIn =
CASE
WHEN CHARINDEX('/',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(charindex('/',OrderGauge)-1))
WHEN CHARINDEX('-',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(CHARINDEX('-',OrderGauge)-1))
WHEN CHARINDEX('MIN',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(CHARINDEX('MIN',OrderGauge)-1))
WHEN CHARINDEX('MAX',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(CHARINDEX('MAX',OrderGauge)-1))
WHEN CHARINDEX('NOM',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(CHARINDEX('NOM',OrderGauge)-1))
WHEN CHARINDEX('ACT',OrderGauge)>0 THEN SUBSTRING(OrderGauge,1,(CHARINDEX('ACT',OrderGauge)-1))
else ''
end
Go
*/
Order gauge is a text field in the database. The text looks like this: .025/.026 or .025 MIN
I am basically parsing the numbers based on the characteristics.

I set the KDSales database up as Decimal(6,2). I use cast() function to set up the field.

I have had this problem in the past and it was usually a data issue. This case I have reviewed all the data and it is correct.

Any help would be appreciated. I am willing to rewrite the SP if there is a better way to do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 04:42:10

whats the datatype of DecIn? is it numeric or decimal? if yes, why you set '' as default value? if none of the characters are there, then isnt it enough to just put value of field as it is (ie. OrderGuage) rather than ''?
i think thats the cause of error.

See


select cast('' as Decimal(6,2))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources