Checking if a column has a null value tsql

Question

I'm trying to determine whether a product exists in the product table already. When I run this bit of tsql, it should return 1 and exit out of the proc, because the product id 7777 does not exist in the product table, but it's returning 0. If I run it without the if statement and just execute the select statement, it says that the @prodID is null. I'm wondering why is it not going into the if statement seeing as I am checking if it's a null value. Thanks in advance for any help.

 Declare  @ProdID int

 select @ProdID = dbo.productTbl.ProductID
 from dbo.ProductTbl
 inner join dbo.OrderTbl
 on dbo.ProductTbl.ProductID = dbo.OrderTbl.ProductID
 where dbo.OrderTbl.ProductID = 7777


 if(@ProdID = null)
 begin 
 raiserror('The product does not exist',16,1)
 return 1
 end
 return 0

Answer 1

In SQL Server, use IS NULL to check for a null value rather than = null.

if(@ProdID IS NULL)
begin 
raiserror('The product does not exist',16,1)
return 1
end
return 0

Answer 2

Use IS NULL operator. Also, there is no reason to write code after RAISERROR, it is not executed.

IF @ProdID IS NULL
    RAISERROR('The product does not exist',16,1)

Answer 3

Comparing anything to null results in unkown when using the normal compare operators like =, <>, <= ... which is neither true nor false. The IS operator can compare with null.

if(@ProdID is null)

Answer 4

It is depends on your session settings. The SQL-92 standard states that equality can't occur when one of operands unknown (NULL) and the result of equality operator is unknown in three-state logic which threats as false in binary logic. But you can unset the SQL Server ANSI_NULLS setting (not recommend) and use = operator.

SET ANSI_NULLS OFF
GO
DECLARE @ProdID int
IF(@ProdID = NULL)
  PRINT 'ANSI_NULLS OFF'

Will print ANSI_NULLS OFF

SET ANSI_NULLS ON
GO
DECLARE @ProdID int
IF(@ProdID = NULL)
  PRINT 'ANSI_NULLS OFF'

Nothing to print

SET ANSI_NULLS ON
GO
DECLARE @ProdID int
IF(@ProdID IS NULL)
  PRINT 'ANSI_NULLS ON'

Will print ANSI_NULLS ON