SQL Server and Access condition judgment syntax (casewhen and iff)

Most of the time, for the scalability and accuracy of the program, we will set the corresponding bool type or string type fields in the database to int type. However, we want to display user-friendly content when displaying the content in the program. In this case, we need to convert the searched results into user-friendly results. There are several feasible methods to achieve this purpose.

Most of the time, for the scalability and accuracy of the program, we will set the corresponding bool type or string type fields in the database to int type. However, we want to display user-friendly content when displaying the content in the program. In this case, we need to convert the searched results into user-friendly results. There are several feasible methods to achieve this purpose.

Most of the time, for the scalability and accuracy of the program, we will set the corresponding bool type or string type fields in the database to int type. However, we want to display user-friendly content when displaying the content in the program. In this case, we need to convert the searched results into user-friendly results. There are several feasible methods to achieve this purpose. You can determine the output directly as required during the query, or you can change the results in the program after the query results are displayed. This article discusses the first method.

The SQL Server database and the Access database use different statements, which are described as follows:

SQL Server:

UseCase whenStatement.

Syntax:

CaseDatabase Field

WhenCondition 1ThenResult 1:

WhenCondition 2ThenResult 2:

......

ElseResult when none of the above conditions are met

End

Click here or here to view the description of msdn.

Example: select UserName, case UserRole when '0' then' normal user 'when' 1 'then' administrator 'end as user role from Users

Truncation string by length: select case when LEN (Title)> 20 then SUBSTRING (Title,) else Title end as Title from Articles

Access:

Use the iff () function.

Syntax:

IIf (Expr, truepart, falsepart)

Click here to view the Introduction to Microsoft Office.

Description of IIf function syntax parameters:

ExprRequired. The expression for calculating the value.

TruepartRequired.ExprIsTrueValue or expression returned.

FalsepartRequired.ExprIsFalseValue or expression returned.

If a condition is set, the system returns the correct result. If the condition is not set, the system returns an error.

Note:

Although IIf only returnsTruepartAndFalsepartBut the two parts are always calculated. Therefore, pay attention to the unnecessary side effects. For exampleFalsepartIf the divisor is zeroExprIf this parameter is set to True, an error is also returned.

Example: select iif (IfPaid = 1, 'Yes', 'no') as whether the payment has been made from Accounts

Truncation string by length: select iif (LEN (Title)> 20, left (Title, 20), Title) as Title from Articles

Note:

If you want to bind data to the DataGridView, and the column of the DataGridView has been defined in advance, assign the as definition of the column that obtains the value through this method in the query statement to the DataPropertyName of the corresponding column to display the queried value. For example, assign the Title value of the preceding statement to the DataPropertyName of the corresponding column added by the DataGridView to display the value of the first 20 characters after the original Title column in the database.