SQL Server case when... else... end result type

Source: Internet
Author: User
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.