InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:30:20 GMT20http://www.sqlservercentral.com/Forums/Topic1421541-149-1.aspxThanks, used the second solution and works perfectlyTue, 19 Feb 2013 04:46:58 GMTaldochttp://www.sqlservercentral.com/Forums/Topic1421541-149-1.aspxit think the way you have written the query is not rightcase statement should be the part of the expressionin your case you can use case like this select * from sys.objects where name=(case when name='t1 ' then 'condition ' else 'condition2' end)Tue, 19 Feb 2013 04:41:09 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1421541-149-1.aspxYou can fix the syntax like this: -[code="sql"]DECLARE @Dept AS INTSET @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 );[/code]However, this isn't the best way for the query optimiser to work, see [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]Gail's blog post here --> http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Instead, do something like this: -[code="sql"]DECLARE @Dept AS INT;SET @Dept = 40;IF @Dept = 0BEGIN SELECT * FROM plu_file_dates WHERE pluid IN (SELECT pluid FROM plu_file WHERE ToDept = 60 );ENDELSE BEGIN SELECT * FROM plu_file_dates WHERE pluid IN (SELECT pluid FROM plu_file WHERE ToDept = 60 AND FromDept = @Dept );END[/code]Tue, 19 Feb 2013 04:36:22 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1421541-149-1.aspxI am trying to select different data from a table depending on what the passed parameter value is. If the parameter is 0 then select all products from plu_file_dates with ToDept value of 60 in the plu_file or else select all products with ToDept of 60 and FromDept with value matching parameter.However no matter how I try to write the where statement it always fails to executeDECLARE @Dept AS INTSET @Dept = 40 SELECT * FROM plu_file_dates WHERE pluid in (SELECT pluid FROM plu_file WHERE (CASE WHEN @Dept = 0 THEN (ToDept = 60) ELSE (FromDept = @Dept and ToDept = 60) END)) Tue, 19 Feb 2013 04:25:29 GMTaldoc