I'm working on this sproc:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ap_GetProducts
	(@ProductId                     INT = NULL,
	 @Product_Series            VARCHAR(100)= NULL,
	 @Product_Number          VARCHAR(20)= NULL,
	 @Product_Name             VARCHAR(100)= NULL,
	 @Product_Description     VARCHAR(MAX)= NULL)

AS
BEGIN

	SELECT *
		FROM HPProductsData
		WHERE ProductId				= ISNULL(@ProductId, ProductId) AND
			  Product_Series		= ISNULL(@Product_Series, Product_Series )AND
			  Product_Number		= ISNULL(@Product_Number, Product_Number) AND
			  Product_Name			= ISNULL(@Product_Name, Product_Name) AND
			  Product_Description	= ISNULL(@Product_Description, Product_Description)
	
END

When I run it like this:

EXEC ap_GetProducts    -- No params passed

I get back 1310 records

If I run this

SELECT * FROM HPProductsData

I get back 44786 records

if I do this

SELECT * FROM HPProductsData WHERE Product_Name != NULL

I get back 1310 records


So, my query is only returning records with a non-null Product_Name. I have SET ANSI_NULLS OFF.