TSql Case within Case

Question

I am trying to do a select statment in tsql with a case within a case. First is based on what the SearchField is. Next I need to do it based on the 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 

Note what I am trying to do is within the case statement do a select.

I get a message saying Incorrect syntax near the keyword 'case'.

Answer 1

Based on your example, you probably want to use IF statements (here is an example with ELSE, but you can use IF and ELSE IF multiple times too):

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 is very similar, but is rather used with inline statements (i.e. SELECT CASE 1=1 THEN 'a' ELSE 'b' END).