case in sql stored procedure on SQL Server

Question

Based on a parameter I d like to execute a different Update in my stored procedure   I ve tried many permutations of the code below  but I always have errors        EmpID int   0    NewStatus nvarchar 10    0 AS BEGIN SET NOCOUNT ON   select CASE    NewStatus   when     InOffice  then       Update tblEmployee set InOffice   -1 where EmpID    EmpID when    OutOffice  then      Update tblEmployee set InOffice   -1 where EmpID    EmpID when  Home  then      Update tblEmployee set Home   -1 where EmpID    EmpID  END

User · Answer

SELECT CASE WHEN  SELECT  Salary FROM tbl Salary WHERE Code 102 AND Month 1 AND Year 2020   0 THEN  Pending  WHEN  SELECT  Salary FROM tbl Salary WHERE Code 102 AND Month 1 AND Year 2020 AND   lt  gt 0 THEN  SELECT CASE  WHEN ISNULL ChequeNo 0  IS NOT NULL   THEN  Deposit  ELSE  Pending  END AS Deposite FROM tbl EEsi WHERE  AND  Month  1  AND  Year   2020  AND  END AS Stat

User · Answer

CASE isn t used for flow control    for this  you would need to use IF     But  there s a set-based solution to this problem instead of the procedural approach   UPDATE tblEmployee SET    InOffice   CASE WHEN  NewStatus    InOffice  THEN -1 ELSE InOffice END    OutOffice   CASE WHEN  NewStatus    OutOffice  THEN -1 ELSE OutOffice END    Home   CASE WHEN  NewStatus    Home  THEN -1 ELSE Home END WHERE EmpID    EmpID   Note that the ELSE will preserves the original value if the  NewStatus condition isn t met

User · Answer

Try this  If  NewStatus     InOffice   BEGIN      Update tblEmployee set InOffice   -1 where EmpID    EmpID END Else If  NewStatus     OutOffice  BEGIN     Update tblEmployee set InOffice   -1 where EmpID    EmpID END Else If  NewStatus     Home  BEGIN     Update tblEmployee set Home   -1 where EmpID    EmpID END

[sql-server] case in sql stored procedure on SQL Server

Examples related to sql-server