Simple multi-condition query by hyw

Source: Internet
Author: User

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.

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.