CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Spider
Spider

Reputation: 524

Case Statement With Between Clause In Sql Server

I am used to get parameters to my Stored Procedure with a default value ''. I want to check whether if the Date is selected and , if so I want to Execute My Between Clause. In Normal Scenario I am gonna do like this

SELECT tbl1.Column1,tbl1.Column2 FROM table1 tbl1
WHERE tbl1.Column1 = CASE WHEN @Column1Val = '' THEN tbl1.Column1 ELSE @Column1Val END

But I can't Do this with Between Clause. I can't figure a way to do this other than dynamic query. Is there a way other than Dynamic Query?

This is what I am Trying to do

SELECT tbl1.Column1,tbl1.Column2 FROM table1 tbl1
WHERE tbl1.txnDate = CASE WHEN @DateTo = '1900-01-01' AND @DateFrom = '1900-01-01' 
                          THEN  CAST(tbl1.txnDate AS DATE) 
                          ELSE CAST(tbl1.txnDate  AS DATE) BETWEEN @DateTo AND @DateFrom 
                     END

Upvotes: 0

Views: 3448

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

Try this:

SELECT tbl1.Column1,tbl1.Column2 
FROM table1 tbl1
WHERE 
(@DateTo = '1900-01-01' AND @DateFrom = '1900-01-01')
OR 
(
 NOT (@DateTo = '1900-01-01' AND @DateFrom = '1900-01-01') 
 AND (CAST(tbl1.txnDate  AS DATE) BETWEEN @DateFrom AND @DateTo)
 )

From what I understand, '1900-01-01' is your default value for start and end dates, so you only need a filter if the user has selected some non-default values for start and end dates. Please let me know if this is what you need.

Demo

Upvotes: 2

Related Questions