CODEWITHSUNDEEP
sql-server
rocky
rocky

Reputation: 161

case in sql stored procedure on SQL Server

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

Upvotes: 16

Views: 119832

Answers (3)

Code
Code

Reputation: 739

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

Upvotes: 0

rs.
rs.

Reputation: 27427

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

Upvotes: 36

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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.

Upvotes: 19

Related Questions