This problem originated from csdn. Later I found that SQL Server versions have different processing methods for this case when else end result ..
The case syntax is as follows: /**/ /*
Case input_expression
When when_expression then result_expression
[N]
[
Else else_result_expression
]
End
Searched case function:
Case
When boolean_expression then result_expression
[N]
[
Else else_result_expression
]
End
*/
Online documents include texts: result type: the type with the highest priority is returned from the type set of result_expressions and optional else_result_expression.
For more information, see data type priority as follows: /**/ /*
SQL Server uses the following precedence order for data types:
1. User-defined data types (highest)
2. SQL _varian t
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)
*/
Prepare the test data and start the test as follows:
Declare @ T Table (ID Char ( 10 ))
Insert Into @ T Select ' 1 '
Insert Into @ T Select ' 2 '
Insert Into @ T Select ' 3 '
-- The specified result is displayed as char type and the length is 10. The result type after case is Char and remains unchanged.
Select Case ID When ' 3 ' Then Cast ( '' As Char ( 10 )) Else ID End As ID,
SQL _variant_property (( Case ID When ' 3 ' Then Cast ( '' As Char ( 10 )) Else ID End ), ' Basetype ' ) As Caseresulttype,
Case ID When ' 3 ' Then Datalength ( Cast ( '' As Char ( 10 ))) Else Datalength (ID) End As Iddatalength
From @ T
/**/ /*
Id caseresulttype iddatalength
1 char 10
2 char 10
Char 10
*/
-- If the specified type is not displayed, ''is of the varchar type. Based on the result type priority of the online document case, varchar takes precedence over char,
-- The result type after case is varchar.
Select Case ID When ' 3 ' Then '' Else ID End As ID,
SQL _variant_property (( Case ID When ' 3 ' Then '' Else ID End ), ' Basetype ' ) As Caseresulttype,
Case ID When ' 3 ' Then Datalength ( '' ) Else Datalength (ID) End As Iddatalength
From @ T
/**/ /*
Id caseresulttype iddatalength
1 varchar 10
2 varchar 10
Varchar 0
*/
-- If the specified type is not displayed, the value 4 is int type. Based on the result type priority of the online document case, int prevails over char,
-- The result type after case is int, but the storage size is not all 4.
Select Case ID When ' 3 ' Then 4 Else ID End As ID,
SQL _variant_property (( Case ID When ' 3 ' Then 4 Else ID End ), ' Basetype ' ) As Caseresulttype,
Case ID When ' 3 ' Then Datalength ( 4 ) Else Datalength (ID) End As Iddatalength
From @ T
/**/ /*
Id caseresulttype iddatalength
1 int 10
2 int 10
4 int 4
*/
My test environment is
Microsoft SQL Server 2008 (SP1) - 10.0 . 2531.0 (Intel x86) Mar 29 2009 10 : 27 : 29 Copyright (c) 1988 - 2008
Microsoft Corporation Standard Edition On Windows NT 5.2 < X86 > (Build 3790 : Service Pack 2 )
currently, it is found that different versions, including minor versions, may have different results. I don't know if these versions do not conform to the online document description. Can they be called bugs?
if you are interested, you can test and paste the results of your SQL version.