CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-server-2014
Glory Raj
Glory Raj

Reputation: 17701

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

Upvotes: 2

Views: 146

Answers (3)

r.malekmohammadi
r.malekmohammadi

Reputation: 19

you can use this 

   SELECT   Name 
    FROM     pass_activationinfo 
    WHERE 
        (( @role = 'Admin'
             and  account_id = @AccountId)
             or Activated_By = @Activated_By)

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

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

Upvotes: 6

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Change the case and use OR instead

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

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


END

Upvotes: 2

Related Questions