sql

[Wanna]

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?

[Old Pedant]

And what database is this??

If it is MySQL, you can't use + to concatenate strings. You *must* use the CONCAT function.

And your code doesn't really make sense: What will the value of Geheugen be when the CASE is *not* true? You don't provide an ELSE for other values.

And if Geheugen in the table is a numeric type (it must be, based on the error message), then why do you need the cast? Numeric types can only be null or numbers, they can never be a blank string.

So...

SELECT CASE WHEN Geheugen IS NOT NULL THEN CONCAT(Geheugen,'mb')
ELSE 'n/a'
END AS Geheugen
FROM table
WHERE id = 66

You provide whatever value you want there for the ELSE, in place of my 'n/a'

**********

HOWEVER...

I don't think this is MySQL, since you used [Geheugen] in one place, and that's more likely to be SQL Server, yes?

And SQL Server doesn't allow you to CONCAT text and numbers, so this is for SQL Server:

SELECT CASE WHEN Geheugen IS NOT NULL THEN CONVERT(VARCHAR(10),Geheugen) + 'mb'
ELSE 'n/a'
END AS Geheugen
FROM table
WHERE id = 66


***********

EDIT: I just noticed the comment about "from SSMS", so of course this is indeed SQL Server. But next time say what DB you are using. As you can see, it makes a difference.

[Wanna]

I'm sorry, it is indeed SQL Server.
I already solved it with help of a other forum (The SQL Server forums :P)

Here is the code that helped:


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

The value will be either a numeric or a null.

If the value is a null it will return null (This is how my code works :P)
If it isn't null then it must return a varchar string, 2 mb in this case.

I tried this on several ways before i asked here and on the SQL Server forum but they helped me with a working code.

[Old Pedant]

You still are *NOT* handling the case when ISNULL(CAST([GS].[Geheugen] as varchar(50)),'')!='' is false!

When the field is indeed NULL, then you will have NO VALUE AT ALL for the final value of Geheugen.

On top of that, my code is more efficient. It doesn't need to do a CAST twice. (To be fair, the code optimizer in SQL Server will probably only actually do the CAST once, but you can't know that.)

There's really not a much more efficient comparison in SQL Server than IS NULL or IS NOT NULL.

Geheugen IS NOT NULL

is going to execute much faster than the code you show.