Multi-condition query Program

When querying users, you may also use a variety of query conditions, such as by employee ID, by name, by gender, or by education level. It is also possible to query by a combination of multiple conditions, such as checking the educational background as a female employee of a junior college.
For such queries, you can enter query conditions and then combine SQL statements to perform queries. For example, you can enter the employee ID and name, and click Submit to obtain the information in the background, as shown in the following code:
Copy codeThe Code is as follows:
// Set the query statement
String strSql = "SELECT * FROM [user] where UserState = 1 ";
// Add query conditions if the user name is not empty
If (UserName! = "")
{
StrSql + = "and (username' =" + UserName + "')";
}
// If the gender is not empty, add query conditions.
If (Sex! = "")
{
StrSql + = "and (Sex '=" + Sex + "')";
}

After creating an SQL statement, run the statement to obtain the query result.
This method is the most widely used and the least secure, because it is the most vulnerable to SQL injection attacks.
If you want to avoid SQL injection attacks, you can write the query statement in the stored procedure, and then use SqlParameter to pass the parameter to the stored procedure. However, how do you write a stored procedure for a Multi-condition query?
In fact, this stored procedure is not difficult. You can use the following methods:
Copy codeThe 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