CODEWITHSUNDEEP
sqlstored-procedures
grmihel
grmihel

Reputation: 846

Bit parameter with Null value in Stored Procedure

I'm having a bit value in my table, which contains bit (0 or 1) and NULL (as default).

Here is my SProc:

CREATE PROCEDURE msp_CustomerStatistics 
   @Postal_MinValue int,
   @Postal_MaxValue int,
   @SubscriberState bit,
   @CustomerType varchar(50)
BEGIN
   [...]
   WHERE Sub = @SubscriberState
   AND Postal BETWEEN @Postal_MinValue AND @Postal_MaxValue
   AND CustType = @CustomerType
END

When I pass the @SubscriberState parameter with 1 or 0, the result is correct. But when I pass null, the result is 0, which ain't correct.

If I create a SQL select with following where clause:

WHERE Sub IS NULL

Then the result shows the correct count.

Any idea how I make my Stored Procedure working with NULL parameter in my WHERE clause too??

Upvotes: 2

Views: 6276

Answers (2)

Dan
Dan

Reputation: 10680

You can not use the = operator with null values. Comparisons with NULL always return false. Try to modify your WHERE statement to the following:

WHERE (Sub = @SubscriberState OR (@SubscriberState IS NULL AND Sub IS NULL))

Upvotes: 5

Chris
Chris

Reputation: 2952

You could either set null values to 0 and check it like this:

WHERE Isnull(Sub,0) = @SubscriberState

or have a tri-state sort of bodge like:

WHERE Isnull(Sub,3) = isnull(@SubscriberState,3)

Upvotes: 1

Related Questions