SQL Server solves multiple conditional fuzzy query problems using case _mssql

In our project development, we often encounter the requirements of multiple conditional fuzzy queries. There are two common solutions for this: one is to stitch the SQL string at the end of the program, construct the corresponding SQL string based on whether a condition is selected, and use the dynamic SQL statement in the stored procedure of the database. The essence is also the concatenation of SQL strings, but from the end of the program to the database side.

The disadvantage of these two approaches is obvious: one is to use multiple if statements to judge when multiple conditions are nullable, and the second is to create SQL injection vulnerabilities by stitching SQL statements.

Recently write database stored procedures often use the case when statement, just can use this statement to solve the above problems. In the case of the Northwind database in SQL, I want to manipulate the Employees table in which the default data is as follows:

Use the following script to query the data in the table:

Code

 DECLARE @FirstName NVARCHAR (), 
     @LastName  NVARCHAR (); 
 SELECT @FirstName = ', 
     @LastName = '; 
 SELECT * 
 from  Employees C 
 WHERE CHARINDEX ( 
       case 
           @FirstName = ' THEN FirstName
           ELSE @FirstName
         End
       ),
       FirstName
     > and 
     CHARINDEX (case
         @LastName = ' THEN LastName ELSE @LastName end),
         LastName
       

After execution, it will be found that the result is the same as Figure 1.

We assign the @firstname variable in the second row to ' n ' to try and find out all the records in the FirstName field that contain the string ' n ', as shown in the following figure:

If we try to assign the @lastname variable of the third row to ' d ', the result will be that all FirstName fields contain the ' n ' and LastName fields containing ' d ' records, as shown in the following figure:

Through the above example we can see that by giving two variables to pass different values, can be based on multiple conditions for fuzzy query, if the above statement written in the stored procedure, you can no longer have to splice the SQL statement, there will be no injection problem.

The above script's simple description: Replaces like with the CHARINDEX function, avoids stitching up the SQL statement, uses the case time statement, when passes the parameter value is the empty string to let the condition always be true, is equal to ignore this condition, is not the empty string when the parameter value fuzzy query.

The above is the work of the summary of experience, hope to help everyone. There are some more practical uses when it's time to write.

Here's a description of the SQL case multi-conditional usage

Case has two formats. Simple case function and case search function.

--Simple case function case sex when ' THEN ' man ' when '
     THEN ' woman ' Else '
other ' end
--case search function case when
sex = ' THEN ' when
     sex = ' THEN ' woman ' Else '
other ' end

In both of these ways, you can achieve the same functionality. The simple case function is relatively concise, but there are some limitations to the function, such as writing a judgment, compared with the search function.

There is also a problem to note that the case function returns only the first qualifying value, and the remainder of the case is automatically ignored.

For example, the following SQL, you can never get the "second class" result

Case when Col_ in (' A ', ' B ') THEN ' first class ' When
     Col_ in (' a ')    THEN ' Second class '
else ' other ' end

Let's take a look at what we can do with the case function.

First, the known data is grouped and analyzed in a different way.

Have the following data: (in order to see more clearly, I did not use the country code, but directly using the country name as primary Key)

Country (country) population (population)

China

United States

Canada

United Kingdom

France

Japan

Germany

Mexico

India

According to the population data of this country, the population of Asia and North America is counted. Should get the following result.

Continent population

Asia

North america

Other

What would you do to solve the problem? Creating a view with state code is a solution, but it is difficult to dynamically change the way statistics are used.

If you use the case function, the SQL code is as follows:

SELECT SUM (population), case country when '
        China '   THEN ' Asia ' when
        ' India '   THEN ' Asia ' when
        ' Japan   ' THEN ' Asia ' when
        ' us '   THEN ' North America ' when
        ' Canada ' THEN ' North America ' when
        ' Mexico ' THEN ' North America '
    Else ' other '
end  table_a
GROUP by case country when
        ' China '   THEN ' Asia ' when
        ' India '   THEN ' Asia ' when
        ' Japan '   THEN ' Asia '
        When ' American '   THEN ' North America ' when
        ' Canada ' THEN ' North America ' when
        ' Mexico ' THEN ' North America '
    Else ' other ' end;

Similarly, we can use this method to determine the level of wages, and statistics of the number of each level. The SQL code is as follows;

SELECT case when
    salary <= THEN "when
       salary > and salary <=  " when
       THEN > and Salary Y <=  THEN ' when
       salary > and salary <= THEN '
    ELSE NULL-end Salary_class,
    COUNT (*)
From
   table_a GROUP by case when
    salary <= THEN "when
       salary > and salary <=  "
       when Salary > and Salary <=  THEN ' when
       salary > and Salary <= '
    ELSE NULL-end;

Second, use a SQL statement to complete the grouping of different conditions.

Have the following data

National (country) gender (sex) population (population)

China

China

United States

United States

Canada

Canada

United Kingdom

United Kingdom

Grouped by country and gender, the results are as follows

National men and women

China

United States

Canada

United Kingdom

In general, the Union can also be implemented with a statement to query. But that increases consumption (two select parts), and the SQL statement is longer.

Here is an example of using the case function to complete this function

SELECT country,
    sum (case when sex = "THEN 
           population ELSE end),--Male population
    sum (case when sex = ' THEN 
           pop Ulation ELSE End)  --female population from
table_a
GROUP by country;

In this way, we use Select to complete the output form of two-dimensional table, fully show the powerful case function.

third, use the case function in check.

Using the case function in check is a very good solution in many cases. There may be a lot of people who don't check at all, so I suggest you try using check in SQL after looking at the example below.

Now let's take an example

Company A, this company has a rule, the female staff's salary must be higher than the block. If you use check and case to behave, as shown below

CONSTRAINT check_salary Check
      (case when sex = "THEN case when
         salary > 
            THEN else end Else"
         = )

If you simply use check, as shown below

CONSTRAINT check_salary check
      (sex = ' and salary >)

The condition of the female staff was met, and the male staff could not enter it.

One of my examples: <br>select (case when t.name= ' name ' THEN ' OK ' ELSE ' no ') as mycom, and Jname from t<br> defines a new field Displays different display results for field results, like Switch...case