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
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With