CODEWITHSUNDEEP
sql-server-2008t-sqlstored-procedures
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

If Else in where clause

Here is a snippet of what I have:

select something from myTable
where curentFlag = 'Y'
and
case when @Year2 is not NULL then  
 AYEAR >= @Year AND AYEAR <= @Year2
else
 AYEAR= isnull(@Year ,AYEAR)  
end
        ADATE = ISNULL(@Date, ADATE)

But this yields:

Incorrect syntax near '>'.
Incorrect syntax near 'ADATE'.

The user should be able to search by a year (equals to) or a year range. So I'm either passing in just @YEAR or both @YEAR and @YEAR2. So let's say my data set:

DECLARE @y TABLE(AYEAR INT);
INSERT @y VALUES(2008),(2010),(2010);

Now I have these variables:

DECLARE @YEAR INT, @YEAR2 INT;

Upvotes: 0

Views: 1821

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

CASE is an expression that returns a single value. It cannot be used for control of flow logic.

If you are sure that @Year will always be populated and @Year2 will only sometimes be populated, than this much simpler logic should work:

WHERE CURRENTFLAG = 'Y'
AND AYEAR BETWEEN @Year AND COALESCE(@Year2, @Year)
AND ADATE = COALESCE(@Date, ADATE);

You can try it with a very simple example:

DECLARE @y TABLE(AYEAR INT);
INSERT @y VALUES(2008),(2010),(2010);

DECLARE @YEAR INT = 2008, @YEAR2 INT = 2010;
--DECLARE @YEAR INT = 2008, @YEAR2 INT = NULL;
--DECLARE @YEAR INT = 2010, @YEAR2 INT = NULL;

SELECT AYEAR FROM @y 
  WHERE AYEAR BETWEEN @YEAR AND COALESCE(@YEAR2, @YEAR);

Upvotes: 3

Nick
Nick

Reputation: 4212

 where CURRENTFLAG = 'Y' 
    case when @Year2 is not NULL then  --after THEN should be statement or BEGIN block not AND
 and AYEAR >= @Year AND AYEAR <= @Year2
    else
     AND  AYEAR= isnull(@Year ,AYEAR)   -- here also
    end
        and    ADATE = ISNULL(@Date, ADATE)

Upvotes: 0

Related Questions