What is the result for this query although there null in address column SELECT EMPLOYEENAME,EMPLOYEEADDRESS FROM EMPLOYEE WHERE ADDRESS=NULL
Ofcourse will get a result set with no datarows
Write a query like
SELECT EMPLOYEENAME,EMPLOYEEADDRESS FROM EMPLOYEE WHERE ADDRESS=NULL
will get a result set with zero rows of data although there is rows having value null in the address column. So to get the correct result set we can set like this below
set ANSI_NULLS OFF
then execute the query we will get the correct result set
SELECT EMPLOYEENAME,EMPLOYEEADDRESS FROM EMPLOYEE WHERE ADDRESS=NULL
But again queston rise in many of our minds, why we will use a syntax which is not a generally used by most of the people. yes you r right but in some situation like while writting a script,storeprocedure,funtions we will face some problem.
Example:
CREATE PROCEDURE test(
@phone VARCHAR(20)
) AS
BEGIN
SELECT * FROM COMPANY WHERE phone is @phone
END
we can not write a query like this . we will get a error.
so at this point of time we need the result set having the phone column having value as @phone i.e what ever may be the value of @phone(null/7987/…..)
so in this case we need the help of this syntax.
SET ANSI_NULLS off
SELECT * FROM COMPANY WHERE phone = @phone
if you are interested for more knowledge then visit:
http://msdn.microsoft.com/en-us/library/ms188048.aspx