Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to achieve multi-conditional query". The content of the explanation is simple and clear, and it is easy to learn and understand. let's follow the editor's train of thought to study and learn "how to achieve multi-conditional query"!

In the query of users, a variety of conditions may also be used, such as through the job number query, through the name query, through gender query, through education query and so on. It is also possible to inquire through a combination of conditions, such as checking female employees with college education and so on.

For this kind of query, the usual method is to let the user enter the query conditions, and then combine the SQL statements to query. For example, let the user enter the job number, name, etc., and then click the submit button to get the information in the background, as shown in the following code:

The copy code is as follows:

/ / set query statement

String strSql = "SELECT * FROM [user] where UserState=1"

/ / add a query condition if the user name is not empty

If (UserNameplate = "")

{

StrSql + = "and (UserName'=" + UserName+ "')"

}

/ / add query conditions if the gender is not empty

If (excursion = "")

{

StrSql + = "and (Sex'=" + Sex+ "')"

}

After creating the SQL statement, execute the statement to get the query results.

This is the most frequently used and insecure method, because it is one of the easiest ways to get others into SQL injection attacks.

If you want to avoid SQL injection attacks, you can write the query statement in a stored procedure and then use SqlParameter to pass parameters to the stored procedure, but what about a stored procedure with multiple conditional queries?

In fact, this stored procedure is not difficult and can be used in the following ways:

The copy code is as follows:

CREATE PROCEDURE [dbo]. [UserCheck]

@ UserId varchar (50) = null

@ UserName varchar (20) = null

@ RealName varchar (20) = null

@ Sex bit = null

@ JobTitle varchar (50) = null

@ Organ varchar (50) = null

@ IDCardType smallint = null

@ IDCard varchar (50) = null

@ Mobile varchar (50) = null

AS

BEGIN

Select * from [user]

Where UserId like case when @ UserId is null then UserId else @ UserId end

And UserName like case when @ UserName is null then UserName else @ UserName end

And RealName like case when @ RealName is null then RealName else @ RealName end

And Sex = case when @ Sex is null then Sex else @ Sex end

And JobTitle like case when @ JobTitle is null then JobTitle else @ JobTitle end

And Organ like case when @ Organ is null then Organ else @ Organ end

And IDCardType = case when @ IDCardType is null then IDCardType else @ IDCardType end

And IDCard like case when @ IDCard is null then IDCard else @ IDCard end

And Mobile like case when @ Mobile is null then Mobile else @ Mobile end

END

Thank you for your reading. the above is the content of "how to achieve multi-conditional query". After the study of this article, I believe you have a deeper understanding of how to achieve multi-conditional query. The specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.