Hi,

I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how. 

Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'

Try

Case when m.dispo = 2 then 'Inactive' else Cast(m.value as varchar(11)) end 'Score'

Tom

• Marked as answer by Lezgettdrunk 8 hours 43 minutes ago

This worked, but what and why is Cast required? I was thinking convert was needed.

Thank you sir!

CAST and CONVERT do the same thing but CONVERT has more options. 

Check this blog post for explanation of Tom's suggestion

SQL Server Case/When Data Type problems

Thank you!

In most cases, CAST() and CONVERT() are equivalent.  They are so much alike that they are both contained in the same topic (https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396) in the SQL Server documentation. 

CONVERT() can do a few things CAST() cannot.  CAST is ANSI standard and CONVERT() isn't.  But they are otherwise the same.

To me, but perhaps not others, CAST(m.value as varchar(11)) is easier to read and comprehend than CONVERT(varchar(11), m.value).  So I usually use CAST() unless I need one of the options that CONVERT() provides and CAST() doesn't.  But the choice is a matter of style and/or your organization's SQL coding standards.

So you certainly could do

Case when m.dispo = 2 then 'Inactive' else Convert(varchar(11), m.value) end 'Score'

That would exactly the same thing, produce exactly the same execution plan, and be exactly as efficient as using CAST().

Tom

In most cases, CAST() and CONVERT() are equivalent.  They are so much alike that they are both contained in the same topic (https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396) in the SQL Server documentation. 

CONVERT() can do a few things CAST() cannot.  CAST is ANSI standard and CONVERT() isn't.  But they are otherwise the same.

To me, but perhaps not others, CAST(m.value as varchar(11)) is easier to read and comprehend than CONVERT(varchar(11), m.value).  So I usually use CAST() unless I need one of the options that CONVERT() provides and CAST() doesn't.  But the choice is a matter of style and/or your organization's SQL coding standards.

So you certainly could do

Case when m.dispo = 2 then 'Inactive' else Convert(varchar(11), m.value) end 'Score'

That would exactly the same thing, produce exactly the same execution plan, and be exactly as efficient as using CAST().

Tom

Thank you for the explanation! The only thing I don't understand is why are you cast/convert m.value? I would thing the 'Inactive' needed this since its a string. 

Value is a numerical column, right? We need to have all case expressions to be the same type. 

'Inactive' is a string (varchar(8)) therefore we need to convert m.value into string as well.

To expand a little on Naomi's response, when SQL is generating the query plan (if you are not used to the term "query plan", you can think of generating the query plan as compiling the statement), it has to decide the datatype of every expression in the query.  If the expression has multiple components of different datatypes, it will have to convert one or more of the datatypes so that the datatypes in any given operation are compatible.  It must decide on the final datatype of the result.  So when you write

Select Case When m.dispo = 2 Then 'Invalid' Else m.value End From myTable m

the datatype of 'Invalid' is a char(8) and m.value is an int, but the result must always be the same datatype.  So SQL must decide to either convert 'Invalid' to an int or m.value to a char(8).  Since you did not include a CAST() or CONVERT() telling SQL how to do the conversion, SQL uses the precedence of each datatype to decide which of those to convert.  SQL will always convert a lower precedence to a higher one.  The complete precedence list from highest to lowest is:

1.	user-defined data types (highest)
2.	sql_variant
3.	xml
4.	datetimeoffset
5.	datetime2
6.	datetime
7.	smalldatetime
8.	date
9.	time
10.	float
11.	real
12.	decimal
13.	money
14.	smallmoney
15.	bigint
16.	int
17.	smallint
18.	tinyint
19.	bit
20.	ntext
21.	text
22.	image
23.	timestamp
24.	uniqueidentifier
25.	nvarchar (including nvarchar(max) ) 
26.	nchar
27.	varchar (including varchar(max) ) 
28.	char
29.	varbinary (including varbinary(max) ) 
30.	binary (lowest) 

Notice that char is #28 and int is #16, so SQL will try to convert the char value to an int.  Of course, the value 'Invalid' cannot be convert to an int, which is why you get your error.

To avoid the error, all you do is specify that the int is to be converted something we know the char datatype is compatible with.  So I chose varchar(11) in case m.value was a very large number.  Then I have a char(8) and a varchar(11), so SQL will convert the char(8) value 'Invalid' to a varchar(11).  Of course, that can always be done without generating an error.  So the final datatype will be varchar(11) and the error is avoided.

Tom