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

Today, I will talk to you about how SqlServer uses case when to solve the problem of multi-conditional fuzzy query, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

In the process of project development, we often encounter the requirement of multi-conditional fuzzy query. In this regard, we have two common solutions: one is to concatenate SQL strings on the program side and construct the corresponding SQL strings according to whether a condition is selected or not; the other is to use dynamic SQL statements in the database storage process. Its essence is also to concatenate SQL strings, but it is transferred from the program side to the database side.

The disadvantages of these two methods are obvious: first, when multiple conditions can be empty, multiple if statements are used to judge; second, spliced SQL statements are easy to produce SQL injection vulnerabilities.

Recently, case when statements are often used when writing database stored procedures, which can be used to solve the above problems. Take the NorthWind database in SQL as an example, what I want to manipulate is 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 WHEN @ FirstName =''THEN FirstName ELSE @ FirstName END), FirstName) > AND CHARINDEX ((CASE WHEN @ LastName =' 'THEN LastName ELSE @ LastName END), LastName) >

After execution, you will find that the result is the same as in figure 1.

If we assign the @ FirstName variable in the second line to'n', we will find out all the records that contain the string'n'in the FirstName field, as shown in the following figure:

If we try to assign the @ LastName variable in the third line to'd 'again, we will find out all records in which the FirstName field contains' n 'and the LastName field contains' d', as shown in the following figure:

From the above example, we can see that by passing different values to the two variables, we can make fuzzy queries according to multiple conditions. If the above statements are written in the stored procedure, there is no need to concatenate SQL statements and there will be no injection problems.

The simple description of the above script: replace like with the charindex function to avoid splicing sql statements; use the case when statement to make the condition always true when the passed parameter value is an empty string, which is tantamount to ignoring the condition and fuzzy query according to the parameter value when it is not an empty string.

The above is a summary of my work experience. I hope it will be helpful to all of you. There are also some practical uses of case when. Write again if you have time.

Here is an introduction to the multi-conditional use of SQL CASE.

Case comes in two formats. Simple Case function and Case search function.

Simple Case function CASE sex WHEN 'THEN' male 'WHEN' 'THEN' female 'ELSE' other 'END--Case search function CASE WHEN sex =' THEN 'male' WHEN sex = 'THEN' female 'ELSE' other 'END

These two ways can achieve the same function. The writing of the simple Case function is relatively simple, but compared with the Case search function, there are some functional limitations, such as writing judgment.

One more thing to note is that the Case function only returns the first qualified value, and the rest of the Case will be automatically ignored.

For example, in the following SQL, you will never get the "second category" result.

CASE WHEN col_ IN ('averse,' b') THEN 'first type' WHEN col_ IN ('a') THEN 'second type' ELSE' other 'END

Let's take a look at what you can do with the Case function.

First, the known data are grouped and analyzed in another way.

There is the following data: (to see more clearly, I did not use the country code, but directly used the country name as the Primary Key)

National (country) population (population)

China

America

Canada

The United Kingdom

France

Japan

Germany

Mexico

India

According to the population data of this country, count the population of Asia and North America. You should get the following result.

Continent population

Asia

North America

Other

What will you do if you want to solve this problem? Generating a View with a state Code is a solution, but it is difficult to change the statistical method dynamically.

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 'USA' THEN 'North America' WHEN 'Canada' THEN 'North America' WHEN 'Mexico' THEN 'North America' ELSE 'other' ENDFROM Table_AGROUP BY CASE country WHEN 'China' THEN Asia, WHEN, India, THEN, Asia, WHEN, Japan, THEN, Asia, WHEN, United States, THEN, North America, WHEN, Canada, THEN, North America, WHEN, Mexico, THEN, North America, ELSE, other END

Similarly, we can also use this method to determine the level of wages and count the number of people in each grade. The SQL code is as follows

SELECT CASE WHEN salary AND salary)

If the conditions of the female staff are met, the male staff will not be able to enter.

One of my examples:

SELECT (CASE WHEN t. Nameplate ok' ELSE 'no' END) AS myCom, jname FROM t

Define a new field that is used to display different display results of field results, similar to switch...case

After reading the above, do you have any further understanding of how SqlServer uses case when to solve the problem of multi-conditional fuzzy query? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.