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
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