CODEWITHSUNDEEP
sqlsql-server
chab ratana
chab ratana

Reputation: 23

Sql server select condition case when

I use IF condition and i got to long script.

So I want to use CASE WHEN to make script shorter.

How to change scipt below to use CASE WHEN instead of IF:

DECLARE @SDATE1 DATE='2015-01-01'
DECLARE @EDATE1 DATE='2016-01-01'
DECLARE @ASOF VARCHAR(10)='YES'

IF @ASOF='YES' 

select * from customer 

ELSE 

select * from customer WHERE
CAST(CUSTDATE AS DATE)END 
BETWEEN @SDATE1 AND @EDATE1

Upvotes: 0

Views: 39

Answers (2)

teo van kot
teo van kot

Reputation: 12491

Well how about just write more complex condition

SELECT * 
FROM customer WHERE
(@ASOF != 'YES'
   AND CAST(CUSTDATE AS DATE) BETWEEN @SDATE1 AND @EDATE1)
OR @ASOF = 'YES'

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

DECLARE @SDATE1 DATE='2015-01-01'
DECLARE @EDATE1 DATE='2016-01-01'
DECLARE @ASOF VARCHAR(10)='YES'

SELECT  * 
FROM    customer 
WHERE   (CAST(CUSTDATE AS DATE) >= @SDATE1 AND CAST(CUSTDATE AS DATE) <= @EDATE1 AND @ASOF <> 'YES')
    OR  @ASOF='YES'

I've also removed the syntactically incorrect END, and replaced the between with >= <=. See here for some potential problems with between.

Upvotes: 1

Related Questions