conversion issue - 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

General SQL Server Forums

New to SQL Server Programming

conversion issue

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-25 : 07:44:59

here is block that returns

Msg 8114, Level 16, State 5, Line 11
Error converting data type varchar to numeric.

when i execute the same.

declare @v1 numeric(10)
declare @v2 varchar(10)

begin

set @v1 = 1454545

set @v2 = @v1

select @v2

end

i use sql server management studio 2008 standard.

challenge everything

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-25 : 07:50:28

I've got no error with your example code

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 07:50:28

The error seems to indicate the opposite of what you have in your example. In your example the "set @v2 = @v1" is (implicitly) conveting numeric to varchar. The error message is about failure when trying to convert from varchar to numeric.

You can try to see if you have non-numeric data in your varchar column using:

SELECT * FROM YourTable WHERE ISNUMERIC(yourVarcharColum) = 0

That is not a perfect test, because not all strings that return 1 from ISNUMERIC will convert to a given type of numeric value. Nonetheless, that may tell you something.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-25 : 07:51:59

The error seems to indicate the opposite of what you have in your example

eagle eye

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-25 : 07:52:28

Are you sure it's there?
You are converting an integer to a numeric then a numeric to varchar.
The error is numeric to varchar - don't see how that can happen.

When I run this I don't get an error
This does give your error though
declare @v1 numeric(10)
declare @v2 varchar(10)

begin

set @v1 = '1454545..'

set @v2 = @v1

select @v2

end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources