You can fix the syntax like this: -

DECLARE @Dept AS INT

SET @Dept = 40

SELECT *

FROM plu_file_dates

WHERE pluid IN (SELECT pluid

FROM plu_file

WHERE ToDept = 60 AND FromDept = CASE WHEN @Dept = 0

THEN FromDept

ELSE @Dept END

);

However, this isn't the best way for the query optimiser to work, see Gail's blog post here --> http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D

Instead, do something like this: -

DECLARE @Dept AS INT;

SET @Dept = 40;

IF @Dept = 0

BEGIN

SELECT *

FROM plu_file_dates

WHERE pluid IN (SELECT pluid

FROM plu_file

WHERE ToDept = 60

);

END

ELSE BEGIN

SELECT *

FROM plu_file_dates

WHERE pluid IN (SELECT pluid

FROM plu_file

WHERE ToDept = 60 AND FromDept = @Dept

);

END