During the development process, especially the development of database systems, we often encounter queries with multiple conditions. For example, to query the vehicle expenses of a certain department in a certain period of time, this involves multi-condition query. In fact, there are many methods for multi-condition query, some of which are cumbersome and inefficient. For example, if-else is used to determine a condition and a condition to piece together SQL statements. Here I use a method that I personally think is simple to write and easy to understand:
Create procedure [DBO]. [usp_getfeeinfo]
@ Number nvarchar (50) = NULL, -- license plate number
@ Unit nvarchar (50) = NULL, -- Department
@ Feetype nvarchar (50) = NULL, -- charge type
@ Startdate datetime = NULL, -- start time of the query
@ Enddate datetime = NULL -- Query End Time
As
Begin
Select F. ID,
F. Number,
F. [type],
F. feedate,
Round (F. [money], 2) as [money],
F. status,
F. memo,
C. ID as CID,
C. unit1
From sort f left join car C
On F. Number = c. Number
Where (F. Number = @ number or @ number is null) -- if the @ number parameter is null, this field is not in the query condition. Program .
And (C. unit1 = @ unit or @ unit is null)
And (F. [type] = @ feetype or @ feetype is null)
And (F. feedate >=@ startdate or @ startdate is null)
And (F. feedate <= @ enddate or @ enddate is null)
Order by F. feedate DESC
End
In the program, you only need to input the values of all query conditions.
For example, if it is "all", set the value of this field to: null.