The bitwise operation function of SQL Server cleverly solves multiple-choice queries

The bitwise operation function of SQL Server cleverly solves multiple-choice queries

Data Tables of many business objects in the project have the Status field. Some use the int type to save the status, while others use the varchar type.

Whether Int or varchar is used, it is hard to cope with multiple status queries. For example, in general thinking, The enum settings for customerstatus are as follows:

[Serializable]

Public Enum customerstatus

{

New = 0,

Active = 1,

Overdue = 2,

Sushortded = 3,

Closing = 4,

Closed = 5

}

The status value is stored in the database as an int.

What should I do if I want to search for a customer in the active, overdue, and suincluded statuses on the page? Does the program need to set these three status values?

Concatenate a string and pass it to SQL for processing? Although it can be achieved, it is quite inefficient.

 

Now we provide a standard solution:

(1). All enumerations that may be used as search criteria should be defined in the following bitwise operation.

Public Enum customerstatus

{

New = 1,

Active = 1 <1,

Overdue = 1 <2,

Suincluded = 1 <3,

Closing = 1 <4,

Closed = 1 <5

}

 

(2). During database design, the Status field must be of the int type.

In this way, when we select multiple queries, the value of @ status = customerstatus. Active | customerstatus. Overdue | customerstatus. suincluded

(3) The query statement is as follows:

Select *

From customer

Where [Status] & @ status = [Status]

 

If @ status can be null,

Select *

From customer

Where (@ status is null or [Status] & @ status = [Status])

 

With such a simple statement, you can obtain all data rows that meet the @ status requirements.

Data Tables of many business objects in the project have the Status field. Some use the int type to save the status, while others use the varchar type.

Whether Int or varchar is used, it is hard to cope with multiple status queries. For example, in general thinking, The enum settings for customerstatus are as follows:

[Serializable]

Public Enum customerstatus

{

New = 0,

Active = 1,

Overdue = 2,

Sushortded = 3,

Closing = 4,

Closed = 5

}

The status value is stored in the database as an int.

What should I do if I want to search for a customer in the active, overdue, and suincluded statuses on the page? Does the program need to set these three status values?

Concatenate a string and pass it to SQL for processing? Although it can be achieved, it is quite inefficient.

 

Now we provide a standard solution:

(1). All enumerations that may be used as search criteria should be defined in the following bitwise operation.

Public Enum customerstatus

{

New = 1,

Active = 1 <1,

Overdue = 1 <2,

Suincluded = 1 <3,

Closing = 1 <4,

Closed = 1 <5

}

 

(2). During database design, the Status field must be of the int type.

In this way, when we select multiple queries, the value of @ status = customerstatus. Active | customerstatus. Overdue | customerstatus. suincluded

(3) The query statement is as follows:

Select *

From customer

Where [Status] & @ status = [Status]

 

If @ status can be null,

Select *

From customer

Where (@ status is null or [Status] & @ status = [Status])

 

With such a simple statement, you can obtain all data rows that meet the @ status requirements.