In SQL, how do I resolve zeros in a parameter?

Question

I have what seems like a very lightweight SQL query that I cannot understand, and its driving me nuts. This is SQL 2008. Basically, there is a status field where you can select “pending,” “satisfied,” or all. If they send "pending" or "satisfied," there is no problem. But when they choose everything, I have problems. Mostly because I can't figure out how to get records where this field is null in order for it to be displayed (because it should be "equal to null" instead of "= null" (this is what the data is doing; do not control this.)

The code I used does not work for zeros.

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

Try:

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

This will result in a refund of all payments.

Answer 3

Try

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

Answer 4

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

Usually works better than OR

Edit: you want to select the 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) 

Answer 5

You can use coalesce or IsNull in the Payment.StatusCode field, this will allow you to replace a null value with a specific value.

Answer 6

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

Answer 7

There are many approaches depending on the version of SQL server you are using. These articles provide a detailed description: Dynamic Search Conditions in T-SQL

Answer 8

The best way to do this is below. However, you MUST monitor for parametric sniffing. This will become a problem, as your table will become larger and will randomly affect runtime. This is an annoying problem that may appear. 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 

See this article for an article or the value of the snap parameter for google sql code for more information.