In SQL, how do I allow for nulls in the parameter?

Question

I have what seems to be a really easy SQL query I can't figure out and its driving me nuts. This is SQL 2008. Basically, there is a status field where the can pick "pending", "satisfied" or all. If they send in "pending" or "satisfied" there's no problem. But when they pick all I'm having problems. Mostly because I can't figure out how to get the records where this field is null to show up (because it has to be 'is null' instead of '= null'. (This is the way the data will come over; I have no control over that.)

The code I've been using does not work for nulls.

SELECT * FROM Payment_Table where Payment.Status_code = @status_id

Answer 1

You can try

SELECT Col1, Col2,...,Coln --Required Columns
FROM Payment_Table 
where (Payment.Status_code = @status_id OR @status_id IS NULL)

Answer 2

You can use coalesce or IsNull on your Payment.StatusCode field, this will allow you to do a substitution for null with a specific value.

Answer 3

SELECT * 
FROM Payment_Table 
WHERE (Payment.Status_code is null or Payment.Status_code = @status_id) 

Answer 4

Try

WHERE
    ((@status_id IS NULL) OR (Payment.Status_code = @status_id)) 

Answer 5

Try:

SELECT * 
FROM Payment_Table 
WHERE Payment.Status_code = ISNULL(@status_id, Status_code)

This will return all payments.

Answer 6

There are many approaches depending which version of sql server you are using. This articles has an in-depth description: Dynamic Search Conditions in T-SQL

Answer 7

The best way to do this is below. However, you MUST watch out for parameter sniffing. This will become an issue as your table gets bigger and will affect your execution times randomly. This is an annoying issue that can pop up. Use the code below.

CREATE PROCEDURE GetPaymentStatus
@StatusID varchar(50)=NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE @StatusId_local varchar(50)
SET @StatusID_local = @StatusId

SELECT MyField1, MyField2
FROM Payment_Table
WHERE Payment_Table.Status=@StatusID_local 
    OR (@StatusID_local IS NULL AND Payment_Table.Status IS NULL)
END

Check out this article or google sql parameter sniffing for more info.

Answer 8

WHERE Payment_Table.Status = ISNULL(@StatusID, Payment_Table.Status)

It usually works better then OR

Edit: you want to select rows where Payment_Table.Status = NULL when @StatusID = NULL!!

SELECT * FROM Payment_Table where Payment.Status_code = @status_id
UNION ALL
SELECT * FROM Payment_Table where Payment.Status_code IS NULL AND @StatusID IS NULL

OR

...
WHERE
    Payment_Table.Status @StatusID
    OR
    (Payment.Status_code IS NULL AND @StatusID IS NULL)