A few days a few days a few days ago, a problem encountered is a comparison of the NULL value of SQL Server. Under normal circumstances, when we query null or non-null value, use is NULL / IS Not Null, and rarely use = / <>. But in my program, there is no keyword such as IS, but use = / <> such a comparison element symbol, which has encountered some problems. The problem originated from a web query page because the problem is more complicated, so simplifies. On the page, users can freely select certain fields of the data table, fill in the query criteria of the field, first select the comparison operation symbol (=, <>, etc.) and fill in the value. After the submission, you need to create a SQL statement, and each part of the query condition is created by different program modules. Here, the two program modules are involved, and a module is created according to the submission to create a comparison operation symbol, and a module is responsible for creating a comparison value module. There is such a rule in the creation value module, "If the value submitted is empty, set this value to null. But I found that if the ratio is NULL, the same SQL query statement is placed in the stored procedure and the result of the direct query through the application is not the same. Check the SQL Server documentation, discover the comparison of NULL values, there are two rules: There are two rules in the comparison of NULL values in SQL2000. One is the result of the comparison value of the NULL value specified by ANSI SQL (SQL-92) is false, both NULL = NULL value is also FALSE. Another is not allowed to follow the ANSI SQL standard, namely null = null is TRUE. Take a table T as an example.
Table T presents the following data: RowID Data -------------- 1 'Test'2 Null3' Test1 '
According to the ANSI SQL standard, the following two queries do not return any lines: query1: select * from t where data = nullquery2: select * from t where data <> null, follow the non-ANSI SQL standard, query 1 will return the second line Query 2 returns 1, 3 lines. The row of NULL values in the ANSI SQL standard requires the following query: Select * from t where data is null, use is not NULL. This shows that Data = NULL is equivalent to Data IS NULL, DATA <> NULL is equivalent to Data IS NULL.
And the control is used to use the command set ANSI_NULLS [ON / OFF]. The ON value uses ANSI SQL standard, and the OFF value uses non-standard mode. In addition, the SET ANSI_DEFAULTS [ON / OFF] command can also achieve standard switching, but this command is controlled by a set of settings that meet SQL-92 standards, including NULL values.
By default, database management programs (DB-library) are set ANSI_NULLS OFF. But most of our applications have access to the database via ODBC or OLEDB, as an open-compatible database access program, perhaps compatibility, set ANSI_NULLS value set to ON. Some of the problems brought about by this is to pay attention to. Applications such as stored procedures or custom functions are based on DB-Library, by default, SET ANSI_NULLS is OFF, and in such a program, the rules cannot be modified in an environment in such a program, and can only modify the database. Configuration parameters. Consider the following situation. Your application uses AdoDB to access the database, using an OLEDB or ODBC data provider. For the previous query 1: Select * from T where data = null You can send the command to get the result set, or put it in the stored procedure. But their query results are different. If you use the query command directly, what results do not, and if you access the stored procedure, you get the data of the second line.
I wrote a .NET program to verify this. At the same time, it is also necessary to verify the setting of .NET SQLCLIENT's set ANSI_NULLS, since SQLClient is not accessing SQL Server through OLEDB or ODBC, it is directly accessed directly, I think it will use SQL Server default settings, However, the result is exactly the same, its default settings and OLEDB, ODBC.
using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.Odbc; public class AnsiNullsTest {public static void Main (String [] args) {IDbConnection conn; String connType = "Sqlclient"; if (args.length> 0) ConNNTYPE = args [0]; if (ConNTYPE.TOUPPER () == "OLEDB") {console.writeline ("Connection Type: OLEDB"); conn = new OLEDBCONNECTION "Provider = sqloledb.1; user ID = sa; pwd = test; initial catalog = test; data source = test");} else if (connType.toupper () == "ODBC") {Console.WriteLine ("Connection TYPE: ODBC "); conn = new odbcconnection (" driver = {sql server}; uid = sa; pwd = test; Database = test; server = test ");} else {console.writeline (" Connection Type: SqlClclient " ); Conn = new SqlConnection ("Server = test; database = test; user ID = sa; pwd = test");} test (conn);} public static void test (idbconnection conn) {string query1 = "SELECT 'TEST 'WHERE NULL = NULL "; String query2 =" exec p_test "; // is the same SQL statement idbcommand cmd during // store; IDAtareader Reader; Console.Writeline (" Print' Test " 'Set ANSI_NULLS OFF "); try {cmd = conn.createCommand (); conn.open (); cmd.commandtext = query1; reader = cmd.executeReader (); console.writeLine (" Command: " query1); while (Reader.Read ()) {Console.Writeline ("Result:" Reader [0] .tostring ());} reader.close (); cmd.commandtext = query2; reader = cmd.executeReader (); console. WriteLine ("Command:" query2); while (Reader.Read ()) {Console.WriteLine ("Result:" Reader [0] .tostring ());
} Reader.close ();} catch (exception ex) {console.writeLine (ex.Message);} finally {conn.close ();}}} It has a parameter, depending on the parameter, different parameter values Database Access Procedure. The command object made two queries, once a SQL query command, once called the stored procedure. The statement is the same, but the result is different.
