案例中的TSql案例

Question

我正在尝试使用case中的case在tsql中执行select语句。首先是基于什么是SearchField。接下来，我需要基于SearchOper来做这件事。

    declare @searchField varchar(50)  
    declare @searchString varchar(50)
    declare @searchOper varchar(50)

    case @searchField
     when 'CompanyName' then
        case @searchOper
          when 'eq' then
            select * from tbl1 where CompanyName = @searchString
         when 'ne' then 
           select * from tbl1 where CompanyName <> @searchString
         end
     when 'StoreNum' then
        case @searchOper
          when 'eq' then
             select * from tbl1 where StoreNum = @searchString
          when  'ne' then
             select * from tbl1 where StoreNum <> @searchString
        end 
     end 

注意，我要做的是在case语句中执行select。

我收到一条消息，说关键字'case‘附近的语法不正确。

Answer 1

根据您的示例，您可能希望使用IF语句(这里是一个带有ELSE的示例，但您也可以多次使用IF和ELSE IF )：

declare @searchField varchar(50)  
declare @searchString varchar(50)
declare @searchOper varchar(50)

IF @searchField='CompanyName'
BEGIN
    IF @searchOper='eq'
    BEGIN
        select * from tbl1 where CompanyName = @searchString
    END
    ELSE
    BEGIN
        select * from tbl1 where CompanyName <> @searchString
    END
END
ELSE 
BEGIN
    IF @searchOper='eq'
    BEGIN
        select * from tbl1 where StoreNum = @searchString
    END
    ELSE
        select * from tbl1 where StoreNum <> @searchString
    END
END

CASE非常类似，但更倾向于与内联语句一起使用(例如，SELECT CASE 1=1 THEN 'a‘ELSE 'b’END)。

Answer 2

declare @searchField varchar(50)  
declare @searchString varchar(50)
declare @searchOper varchar(50)

 select *
 from tbl1
 where (@searchField <> 'companyName'
       or
       @searchOper = 'eq' and CompanyName = @searchString
       or
       @searchOper = 'ne' and CompanyName <> @searchString)
   AND (@searchField <> 'StoreNum'
       or
       @searchOper = 'eq' and CompanyName = @searchString
       or
       @searchOper = 'ne' and CompanyName <> @searchString)
 option (recompile)

这是一种编写它的方法，它应该执行得很好。您拥有的是www.sommarskog.se/dyn-search.html的一个变体，但是OPTION (RECOMPILE)每次都应该生成一个最优计划。

Answer 3

declare @searchField varchar(50)  
declare @searchString varchar(50)
declare @searchOper varchar(50)

SELECT
    (case @searchField
     when 'CompanyName' then
        (case @searchOper
          when 'eq' then
            (select * from tbl1 where CompanyName = @searchString)
         when 'ne' then 
           (select * from tbl1 where CompanyName <> @searchString)
         end
         )
     when 'StoreNum' then
        (case @searchOper
          when 'eq' then
             (select * from tbl1 where StoreNum = @searchString)
          when  'ne' then
            ( select * from tbl1 where StoreNum <> @searchString)
        end 
        )
     end 
) a

我懒得构建tbl1，改成getdate吧

declare @searchField varchar(50)  
declare @searchString varchar(50)
declare @searchOper varchar(50)

SET @searchField = 'CompanyName'
SET @searchOper = 'eq'
SELECT
    (case @searchField
     when 'CompanyName' then
        (case @searchOper
          when 'eq' then
            (select getdate())
         when 'ne' then 
           (select getdate())
         end
         )
     when 'StoreNum' then
        (case @searchOper
          when 'eq' then
             (select getdate())
          when  'ne' then
            ( select getdate())
        end 
        )
     end 
) a