Select With Case

I am writing a stored procedure. I have a problem on EP.Clerepartition column. I want to select this column based on cases. Where do i need to set my code? I tried this code and I

Solution 1:

Can you please try this way-

SELECT  U.USR_PRENOM ,U.USR_LOGIN,      
CleRepartition=CASEwhen@RoleID=1and@requeteISNOTNULLthen100-@requetewhen EP.Role_Id=2and@requeteISNOTNULLthen20ELSENULLENDFROM [EQUIPE_PROJET] EP   
INNERJOIN UTILISATEUR U ON  U.USR_ID = [User_Id] -- Please define this user id belongs to which table   INNERJOIN Ref_Role_Eq RE ON RE.Role_Eq_Id = [Role_Id] 

Solution 2:

Only thing I could see incorrect is how you are building the case. The syntax for this is correct without any sample data I can't really test.

declare@queryvarchar (250) set@query= (select EP.CleRepartition
from EQUIPE_PROJET EP where EP.Projet_Id=@PROJET_ID AND EP.Role_Id=3and EP.CleRepartition Isnotnull ) 

  SELECT  U.USR_PRENOM ,U.USR_LOGIN, 
  casewhen@RoleID=1and@requete!=NULLthen100-@requetewhen EP.Role_Id=2and@requete!=NULLthen20ELSENULLENDas CleRepartition   FROM [EQUIPE_PROJET] EP   INNERJOIN UTILISATEUR U ON  U.USR_ID = [User_Id]   INNERJOIN Ref_Role_Eq RE ON
RE.Role_Eq_Id = [Role_Id]  END

EDIT:

If you are trying to do a case and bring back the column based on column values it would look more like this:

casewhen **columnFoo=1 then EP.CleRepartition**
     when@RoleID=1and@requete!= NULL then 100 - @requetewhen EP.Role_Id=2and@requete != NULL then 20
     ELSE NULL      END as CleRepartition

this would return the value of EP.CleRepartition when columnfoo was equal to 1