Sql Server 2008中Where条件的案例

问题描述：

如何使用案例和Where？我试过这些线，它显示错误？是否可以使用查询来选择使用参数的where条件。Sql Server 2008中Where条件的案例

在这里，我打算通过'名称'和'类别'，如果名称那么它将像使用名称一样使用操作符，如果'类别'，那么它会选择类别LIKE。

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

答

CASE是一个表达式 - 它必须返回一个值。至少对于SQL Server来说，至少，谓词的结果不是一个值。

尝试使用更简单和/或条件重新写你的查询：

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

Thnks但我们需要specificy最后的4个条件对每个组像（@Query = '名称' AND accessry_name LIKE '％' + @搜索+ '％'）AND COMPANY_ID = @ COMPANY_ID AND branch_ID = @ branch_ID AND division_ID = @ division_ID AND isDeleted = 0） – kbvishnu 2011-06-13 12:06:51

@Harie - 我重新安排了查询以突出显示括在一起的括号 - 现在是否正确？ – 2011-06-13 12:09:34

答

使用或/和条件。那么我认为你不需要案件陈述。

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

答

你也可以创造学习您的查询字符串并使用sp_executesql

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

      exec sp_execute @qrystr

答

在那里你能得到执行它建议的答案工作？如果没有，为什么不尝试先使用CASE测试条件，然后继续查询。请参见下面的代码：

CASE @Query 
    WHEN 'Name' THEN 
    Select * 
    FROM [tblAssetAccessory] assry 
    WHERE assry.accessry_name like '%'[email protected]+'%' 
    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 '%'[email protected]+'%') END) 
     AND [email protected]_ID 
     AND [email protected]_ID 
     AND [email protected]_ID 
     AND assry.isDeleted=0 
     ) 
    END

Sql Server 2008中Where条件的案例

相关推荐