Search code examples
sql-servert-sqlsql-server-2012sql-server-2014

trying to get single column data with switch case statement


I am trying to get query the table based on role like this below but I am getting error like this

Incorrect syntax near '='.

I am not sure why I am getting this error

below is my query

CREATE procedure [dbo].[getName]            
    @AccountId int,    
    @role varchar(50),
    @Activated_By nvarchar(100)            
AS            
BEGIN           

    SELECT   Name 
    FROM     pass_activationinfo 
    WHERE 
        CASE WHEN @role = 'Admin'
             THEN account_id = @AccountId
             ELSE Activated_By = @Activated_By
        END
END

Solution

  • First variant

    SELECT   Name 
    FROM     pass_activationinfo 
    WHERE 
            (@role = 'Admin' AND account_id = @AccountId)
        OR (@role <> 'Admin' AND Activated_By = @Activated_By)
    

    Second variant

    SELECT   Name 
    FROM     pass_activationinfo 
    WHERE 
        CASE
          WHEN @role = 'Admin' AND account_id = @AccountId THEN 1
          WHEN @role <> 'Admin' AND Activated_By = @Activated_By THEN 1
          ELSE 0
        END = 1