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