Must know how to write SQL, multi-conditional query does not spell string

In the project, we often encounter complex query methods, according to the user's input, to determine whether a parameter is legitimate, the legitimate words can be used as a filter, we usually do is to query SQL assigned to a string variable, and then according to the condition of the dynamic stitching where conditions to query. Here's a brief look at the problems and workarounds that are encountered in writing SQL.

An indeterminate field name, and the resulting SQL string concatenation

For example, there is a company to make a system, to support multiple languages, this time we need to store voice information in the database. Then, the corresponding language field is displayed according to the customer's selection query. Let's simulate this scenario, open SQL Server, create a new syslanguage table, add some language fields, such as English,chinese,french, where French doesn't know what it is, it's set to empty.

To build a table of SQL and insert some simple data, SQL is as follows:

CREATE TABLESyslanguage (IdINT PRIMARY KEY, 中文版NVARCHAR( -), ChineseNVARCHAR( -), FrenchNVARCHAR( -))INSERT  intoSyslanguageVALUES(1,'Hello','Hello','Empty')INSERT  intoSyslanguageVALUES(2,' World','World','Empty')INSERT  intoSyslanguageVALUES(3,' Book','Book','Empty')INSERT  intoSyslanguageVALUES(4,'Open','Open','Empty')INSERT  intoSyslanguageVALUES(5,'Save','Save','Empty')INSERT  intoSyslanguageVALUES(6,'New','New','Empty')

So, with system performance in mind, we may use stored procedures to reduce network traffic and improve response times. So, in the stored procedure, we pass in a parameter, and then query the specified language field based on the parameter, so how does SQL write?

This time, we will think in C # code, we are all to put SQL into good, and then passed to SQL Server execution, we can stitch the string ah, and then have the following wording:

DECLARE @sql NVARCHAR( -);DECLARE @para NVARCHAR( -);SET @para = 'Chinese';SET @sql = 'Select ID,' + @para + 'From syslanguage'Print @sqlexec(@sql)

Of course there is no problem with this writing, but when SQL is complicated, a lot of quotes and character escapes, as well as the lack of space in the concatenation of strings, can be a crash! The following is a non-stitching string, the code is as follows:

DECLARE @para NVARCHAR( -);SET @para = 'Chinese';SELECTID, Case @para  when '中文版'  Then中文版 when 'Chinese'  ThenChineseELSEFrenchEND as [Language] fromSyslanguage

Here uses the case when statement to judge, when the judgment condition too much, writes is also very tired, we may write a function to carry on the encapsulation, certainly, the concrete use that method, depends on the concrete situation.

When you say case, you have to mention another query. First look at the needs: the number of boys from the student table, the number of girls and the total number of students .

Seeing this, we first thought of the SUM function, but then what? Seems like something's wrong. So, what to write, of course, when you use case, look at the code

SELECT SUM( CaseStusex when 1  Then 1 ELSE 0 END) asBoys,SUM( CaseStusex when 1  Then 0 ELSE 1 END) asGirls,SUM(STUID) as Total fromStudent

See the code, feeling is not very simple ah, was not thought, hehe!

second, the indefinite query condition produces the SQL string concatenation

Accustomed to C # code, suddenly write SQL, feeling still a bit so uncomfortable! Let's take a look at how SQL writes when there are multiple query conditions, but you're not sure how many query conditions you have. We often encounter this demand, many of the controls on the page, to be based on the user's input query, this time we have to determine whether the value of the incoming control to meet the specified conditions, meet the criteria to be used as a query condition, otherwise ignore this condition.

Concatenation string of the way I believe everyone is often used, here do not write, we are connected to see the cloth splicing SQL how to query!

For example, to query the students of the specified condition, the user may query all, or query the boy, or query the student with the specified ID, or ... Wait, there may be many kinds of situations. Let's write a string of SQL, the code is as follows:

DECLARE @ID INT;DECLARE @Sex BIT;DECLARE @Name NVARCHAR( -);SET @ID = -1;SET @Sex = NULL;SET @Name = 'Ja';SELECT *  fromStudentWHERE(@ID = -1 ORStuid= @ID) and(@Sex  is NULL ORStusex= @Sex) and(@Name  is NULL ORStuname like '%' + @Name + '%')

Here declare three variables, as passed parameters, passed parameters have a default value, or NULL, we add in where to use or add judgment, first Judge @ID =-1, if the condition is established, or will not judge the condition after or, to here (@ID = 1 OR stuid = @ID) Returns a true, equivalent to where true and ..., so it ignores this query condition and continues to follow the filtering judgment below. It's better to write than to stitch a string!

Finally, the role of the n characters in the database, the white is to convert the following string content into Unicode encoding, to write a section of SQL test it

DECLARE @a NVARCHAR( -)DECLARE @b NVARCHAR( -)SET @a =N'English'SET @b = 'English'IF(@a = @b)PRINT 'values Equal'ELSEPRINT 'Not Equal'

The output results are as follows:

In the Chinese version of the system, is equal, in the English version of the system, @b will show garbled. We can test it!

Cloud drizzling

Blog Address: http://www.cnblogs.com/yunfeifei/

Disclaimer: The original text of this blog only represents my work in a certain time to summarize the views or conclusions, and my unit does not have a direct interest in the relationship. Non-commercial, unauthorized, post please keep the status quo, reprint must retain this paragraph statement, and in the article page obvious location to the original connection.

Must know how to write SQL, multi-conditional query does not spell string