Case with Where condition in Sql Server 2008

How to use case and Where ? I tried these lines,it is showing error? Is it possible to use a query which will select the where condition using a parameter.

Here i am tring to pass 'Name' and 'Category', if Name then it will use like opearator with Name ,if 'Category' then it will select LIKE with category.

 Select * 
  FROM  [tblAssetAccessory] assry
  WHERE 
  (CASE @Query
    WHEN 'Name' THEN assry.accessry_name like '%'+@Search+'%' END 
    WHEN 'Category' THEN assry.accessory_CategoryID IN 
        (SELECT asryCat.accessory_CategoryID FROM [Ass开发者_如何学JAVAet].tblAssetAccessory_Category asryCat 
             WHERE asryCat.accessory_Category LIKE '%'+@Search+'%' ) END )
          AND assry.company_ID=@company_ID
          AND assry.branch_ID=@branch_ID
          AND assry.division_ID=@division_ID
          AND assry.isDeleted=0 
          )

CASE is an expression - it has to return a value. And for SQL Server, so far, at least, the result of a predicate isn't a value.

Try re-writing your query using more straightforward AND/OR conditions:

Select * 
FROM  [tblAssetAccessory] assry
WHERE
   (
       (
           @Query = 'Name' AND
           accessry_name like '%'+@Search+'%'
       )
       OR
       (
           @Query = 'Category' AND
           EXISTS (SELECT * from [Asset].tblAssetAccessory_Category asryCat
               where
                   asryCat.accessory_CategoryID = assry.accessory_CategoryID AND
                   asryCat.accessory_Category LIKE '%'+@Search+'%')
       )
   )
   AND
      company_ID=@company_ID
      branch_ID=@branch_ID
      division_ID=@division_ID
      isDeleted=0 

use an or/and condition. I don't think you need a case statement then.

WHERE (@Query = 'Name' AND assry.accessry_name like '%'+@Search+'%') OR (@Query = 'Category' AND (assry.accessory_CategoryID IN 
        (SELECT asryCat.accessory_CategoryID FROM [Asset].tblAssetAccessory_Category asryCat 
             WHERE asryCat.accessory_Category LIKE '%'+@Search+'%' ) )

also you can cerate your query string and execute it using sp_executesql

declare @qrystr nvarchar(500)
set @qrystr = ' Select * 
  FROM  [tblAssetAccessory] assry
  WHERE '
  if @Query = 'Name'
   set @qrystr = @qrystr  + 'assry.accessry_name like ''%'+@Search+'%'' END'
  else 
    set @qrystr = @qrystr  + 'assry.accessory_CategoryID IN 
        (SELECT asryCat.accessory_CategoryID FROM [Asset].tblAssetAccessory_Category asryCat 
             WHERE asryCat.accessory_Category LIKE ''%'+@Search+'%'' ) END )
          AND assry.company_ID=@company_ID
          AND assry.branch_ID=@branch_ID
          AND assry.division_ID=@division_ID
          AND assry.isDeleted=0 
          )'

          exec sp_execute @qrystr

Where you able to get the suggested answers working? If not, why don't you try to test the condition using CASE first, then proceed with the query. See code below:

CASE @Query
    WHEN 'Name' THEN 
    Select * 
    FROM  [tblAssetAccessory] assry
    WHERE assry.accessry_name like '%'+@Search+'%' 
    END 
    WHEN 'Category' THEN 
        Select * 
    FROM  [tblAssetAccessory] assry
    WHERE assry.accessory_CategoryID IN 
         (SELECT asryCat.accessory_CategoryID FROM [Asset].tblAssetAccessory_Category asryCat 
         WHERE asryCat.accessory_Category LIKE '%'+@Search+'%' ) END )
         AND assry.company_ID=@company_ID
         AND assry.branch_ID=@branch_ID
         AND assry.division_ID=@division_ID
         AND assry.isDeleted=0 
         )
    END