Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

case in sql stored procedure on SQL Server

Tags:

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
like image 275
rocky Avatar asked Feb 26 '13 22:02

rocky


People also ask

Can we use case in stored procedure?

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

What is case in stored procedure in SQL?

CASE statements can be used to conditionally enter into some logic based on the status of a condition being satisfied. There are two types of CASE statements: Simple case statement: used to enter into some logic based on a literal value.

Can I use case in where clause SQL Server?

We can use a case statement in Where, Order by and Group by clause. In the Customer table, I have displayed the First Name is Ram or the Last Name is Sharma's salary. So, by using a CASE statement with the where condition displays the result.

Can we use CASE statement in SQL function?

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.


2 Answers

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
like image 199
rs. Avatar answered Sep 27 '22 04:09

rs.


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.

like image 20
Michael Fredrickson Avatar answered Sep 23 '22 04:09

Michael Fredrickson