Error converting varchat 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 varchat to numeric

Author

Topic

WannaKnow
Starting Member

2 Posts

Posted - 2011-09-29 : 03:50:58

USE myDB
/****** Script for SelectTopNRows command from SSMS ******/
SELECT
CASE
WHEN ISNULL(CAST([Geheugen] as varchar(50)),'')!='' THEN [Geheugen] + 'mb'
END as Geheugen,

FROM table
WHERE [ID] = 65

This piece of code gives me the following error: Error converting data type varchar to numeric.

The type from the column 'Geheugen' is numeric.

This code needs to make it to a string a return the column data as: 126mb (The 126 is the value in the database)

How can i fix this problem?

Kristen
Test

22859 Posts

Posted - 2011-09-29 : 04:00:12

" Error converting data type varchar to numeric"

That catches everyone out as the error is "backwards"

In "[Geheugen] + 'mb'" SQL is trying to convert "mb" to a number to add it to [Geheugen]

You need:


SELECT	
CASE
WHEN ISNULL(CAST([Geheugen] as varchar(50)),'')!='' THEN CAST([Geheugen] AS varchar(50)) + 'mb'
END as Geheugen,

FROM table
WHERE [ID] = 65

but you can take out the CASE statement, this will be the same:


SELECT CAST([Geheugen] AS varchar(50)) + 'mb' as Geheugen

WannaKnow
Starting Member

2 Posts

Posted - 2011-09-29 : 04:09:05

No, The case is needed, because sometimes the field will be empty.

Tyvm, Your code worked.

Kristen
Test

22859 Posts

Posted - 2011-09-29 : 06:05:33

"sometimes the field will be empty"

[Geheugen] can;t be empty, its numeric datatype.

If it is NULL then my code will work (i.e. display nothing)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources