Unexpected query results

[Gruuka]

Hello, I am facing a problem with one stored procedure that I made using a method in the WHERE clause that worked in other queries.

ALTER PROCEDURE backend_GetReferrers
	-- Add the parameters for the stored procedure here
	@Filialen_ID		varchar(6) = null,
	@Omschrijving		varchar(100) = null,
	@Actief				tinyint = null,
	@Categorie_ID		int = null
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	From dbo.Referrers rf
	WHERE
		rf.Filialen_ID = case when @Filialen_ID is null then
							rf.Filialen_ID
						 else
							@Filialen_ID
						 end 
		AND rf.Actief = case when @Actief is null then
						rf.Actief
					else
						@actief
					end
		AND rf.Omschrijving like case when @Omschrijving is null then
								rf.Omschrijving
						   else
								 '%' + @Omschrijving + '%'
						   end
		AND rf.ReferrersCategorien_ID = case when @Categorie_ID is null  then
								rf.ReferrersCategorien_ID 
							else
								@Categorie_ID
							end
END

Table layout:

Referrers_ID	int	        not null
Omschrijving	varchar(100)	not null
ReferrersCategorien_ID	int	null
Filialen_ID	varchar(6)	not null
LinkURL	        varchar(500)	null
Tijdstip	datetime	null
Actief	        tinyint	        null
Klikprijs	smallmoney	null
Maandprijs	smallmoney	null

The problem in this query is at the last AND statement, the values of that column in the table itself is all set to NULL but when I run the stored procedure no rows from the table are returned if I remove the last part I get results of that table.

So.. kind of confused here would be awesome if someone could help me out on this problem.

 

[Otto]

AND (rf.ReferrersCategorien_ID is null or rf.ReferrersCategorien_ID = case when @Categorie_ID is null  then
                                rf.ReferrersCategorien_ID
                            else
                                @Categorie_ID
                            end)

or

AND isnull(rf.ReferrersCategorien_ID, 0) = case when @Categorie_ID is null  then
                                isnull(rf.ReferrersCategorien_ID, 0)
                            else
                                @Categorie_ID
                            end

 

[Gruuka]

Cheers! That worked, but just wondering why is the isnull comparison needed? I mean if the values of the table columns are null shouldn't you be able to select on those values?

 

[SQLSister]

Nulls are never equal to each other, so cannot be compared. Null means "I don't know what the information is" to the computer

"NOTHING is more important in a database than integrity." ESquared