CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008null
donde
donde

Reputation: 359

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

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

Upvotes: 4

Views: 8961

Answers (8)

Adriaan Stander
Adriaan Stander

Reputation: 166396

You can try

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

Upvotes: 6

gbn
gbn

Reputation: 432271

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)

Upvotes: 1

Ben Hoffman
Ben Hoffman

Reputation: 8259

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.

Upvotes: -1

Giorgi
Giorgi

Reputation: 30883

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

Upvotes: 0

Miyagi Coder
Miyagi Coder

Reputation: 5532

Try:

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

This will return all payments.

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135011

Try

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

Upvotes: 1

Chris Kannon
Chris Kannon

Reputation: 6091

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

Upvotes: 0

keithwarren7
keithwarren7

Reputation: 14280

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.

Upvotes: 0

Related Questions