T-SQL Query conditions

Question

Hi,

I have a query where account id is will be presented for all the query and all other parameters can be optional. The query can be presented any possible for all other parameters (@old_time,@mounika _time,@min _amount,@Max _amount) can be null or have value. The below query doesn't work in all the conditions based on input.

DECLARE
@Creat _id INT=1,
@old_time DATETIME2='2020-05-11 00:00:00.000',
@mounika _time DATETIME2=NULL,
@min _amount DECIMAL(18,2)=100.00,
@Max _amount DECIMAL(18,2)=NULL

SELECT
*
FROM [dbo].[transaction]
WHERE account_id =@Creat _id and old_time >=@old_time or @mounika _time <=@mounika _time and amount <=@min _amount or amount <=@Max _amount

Answer 1

HI @kccrga ,

Welcome back to Microsoft Q&A!

@mounika _time <=@mounika _time and amount <=@min _amount

Could you please help check above part of your query?

It seems that it might be as below:

new_time <=@new_time and amount >=@min_amount  

You could use column=@paramter or @paramter is null as mentioned by other expert.

Or you could also use column =isnull(@paramter,column ) like below:

SELECT *  
FROM [dbo].[transaction]  
WHERE account_id =@account_id    
and (old_time >=isnull(@old_time,old_time ) and new_time <=isnull(@new_time,new_time))   
and (amount >=isnull(@min_amount,amount) and amount <=isnull(@max_amount,amount))  

If above are not working, please post CREATE TABLE statements for your table ([dbo].[transaction]) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try something like this:

SELECT . . .  
FROM [dbo].[transaction]  
WHERE account_id = @account_id   
and ( @old_time is null or old_time >= @old_time )  
and ( @min_amount is null or amount >= @min_amount )  
and ( @max_amount is null or amount <= @max_amount )  
option ( recompile )  

Also clarify the usage of @mounika _time and introduce it to this condition.