Trivial but also Tricky : Result of using "is NULL" & "= NULL" in T-SQL is different

For the T-SQL (here I use MS-SQL), especially when you write stored procedure, you will normally receive parameter(s) from .NET/Java or any equivalent language to manipulate your client records. This is a normal design of three-tier application (Presentation Layer represents the UI that interact with user; Business Logic layer to code all your business logic; Data Access layer to deal with various kind of data storage apps)

For these received parameters, normally you will use some of it to retrieve (or perform checking or validation) some other data from other tables. You will write any T-SQL to perform SIUD(Select/Insert/Update/Delete) on records based on the business logic in database. In order to SUID on these records, your WHERE-statement must be written precisely, and of concisely. If the WHERE-statement is wrong, then the records SUID-ed will be corrupted.

Let's have simple example:

1) Create a simple table with nullable columns

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_Test]') and

OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[_Test]

GO

CREATE TABLE [dbo].[_Test] (

[col1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[status] [int] NULL ) ON [PRIMARY]

GO

2) Insert 2 sample records into it

insert into _Test(col1, status)

values('First row', NULL)

insert into _Test(col1, status)

values('Second row', 1)

3) Create a stored procedure to determine the record could be found in database or not

IF EXISTS (Select * from sysobjects where name = '_spTest' and xtype = 'P')

DROP PROCEDURE _spTest

GO

CREATE PROCEDURE dbo._spTest

@p1 int

As

IF EXISTS ( Select * from _Test where status = @p1)

PRINT 'Records found !'

ELSE

PRINT 'No records found !'
GO

4) Now try to execute it

execute _spTest 1

execute _spTest NULL

Oh, where's another records?

6) And this is quoted from MSSQL BOL (Book On-Line) / T-SQL Reference:
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.

Now, you realized you used the comparison operators to execute it unknowingly!

7) So, in order to access the particular data accurately, the correct stored procedure should be written (Of course, this is simple scripts only, COALESCE or any other keywords can help you to SIUD the huge records efficiently) as

IF EXISTS (Select * from sysobjects where name = '_spTest' and xtype = 'P')

DROP PROCEDURE _spTest

GO

CREATE PROCEDURE dbo._spTest

@p1 int

As

IF (@p1 IS NULL)

BEGIN

IF EXISTS ( Select * from _Test where status IS NULL)

PRINT 'Records found, with status is null!'

ELSE

PRINT 'No records found with status is null!'

END

ELSE

BEGIN

IF EXISTS ( Select * from _Test where status = @p1)

PRINT 'Records found!'

ELSE

PRINT 'No records found!'

END
GO

8) Now try to execute it again

execute _spTest 1
execute _spTest NULL

9) Correct!