CODEWITHSUNDEEP
sqlsql-servert-sql
Aravind V Shibu
Aravind V Shibu

Reputation: 56

Unexpected output when using a CASE statement

I was trying out some random things using case statement when I stumbled upon this scenario. The first statement throws an error but the second one works fine. Can someone help me understand the execution of CASE statement here.

SELECT CASE WHEN 1 = 1 THEN 'case 1'
            WHEN 2 = 2 THEN 2
            ELSE 10 END

SELECT CASE WHEN 1 = 1 THEN 1
            WHEN 2 = 2 THEN 'case 2'
            ELSE 10 END

Upvotes: 0

Views: 125

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93724

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned(MSDN).

INT has higher precedence than varchar so it tries to convert 'case 1' to int.

Try this u will get the output

SELECT case when 1=1 then 'case 1' when 2=2 then '2' else '10' END

SELECT case when 1=1 then 1 when 2=2 then 'case 2' else 10 END

for more explanation check here

for datatype precedence check here

Upvotes: 1

SylvainL
SylvainL

Reputation: 3938

A case statement cannot return more than one single type. When there is a mix of types such as int and varchar, the type int has a higher precedence and therefore will be the chosen type for the return type of your case statements. See http://msdn.microsoft.com/en-us/library/ms190309.aspx for the list of data type precedence.

Your second case statement will fail if you try to return the second value ('case 2'); for example with:

SELECT case when 1=0 then 1 when 2=2 then 'case 2' else 10 END

Upvotes: 2

Nikko
Nikko

Reputation: 104

All the types of your return value should be the same Read Here

You can also see in the link another link that discusses the precedence to determine which type sql will use on the case statement. And in your case, the type used is int becauase it has a higher priority than text.

The second command worked because the first condition is satisfied. but if you try making it false like "SELECT case when 1=2 then 1 when 2=2 then 'case 2' else 10 END". It will have the same error as command 1 becasue it will evaluate condition 2 as true with return type text that violates the rule on similar types.

In short, the error is caused by not having the same

Upvotes: 1

Related Questions