CODEWITHSUNDEEP
sql-server-2008
Nick Kahn
Nick Kahn

Reputation: 20078

SQL Case statements

update 1: here is my data looks like.

EmployeeId    EmployeeName    Active
12312         bob             0
23432         rob             1
32312         dan             0
43432         jan             1
.........................
.........................

UPDATE:

I am looking after something like this.

EmployeeId    EmployeeName    Active
12312         bob             active
23432         rob             pending
.........................
.........................

I am trying work around the following sql statement and its throwing me an error and not sure if this is the right way of doing...

//error: Incorrect syntax near the keyword 'SET'.

declare @currentStatus nvarchar(50)

Select EmployeeId,EmployeeName,Active,
       set @currentStatus = case when EmployeeId is not null and Active = 0 then "Active"  else "Pending" end as Status
from EmployeeTable

Upvotes: 0

Views: 178

Answers (3)

Yoosaf Abdulla
Yoosaf Abdulla

Reputation: 3978

I think you should try this:

SELECT EmployeeId, EmployeeName, CASE 
       WHEN (EmployeeId IS NOT NULL AND Active = 0) THEN 'Active' ELSE 'Pending' 
       END AS [Status]  
FROM EmployeeTable

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

To get the results you show in your updated question, this is the only query you need. I have no idea why you think you need a local variable to hold the results of the case expression on each row.

SELECT 
  EmployeeId,
  EmployeeName,
  Active,
  [Status] = CASE WHEN EmployeeId IS NOT NULL AND Active = 0 
     THEN 'Active' 
     ELSE 'Pending' END
FROM dbo.EmployeeTable;

EDIT updated with sample data provided in question:

DECLARE @e TABLE(EmployeeID INT, EmployeeName VARCHAR(32), Active BIT);

INSERT @e VALUES
(12312,'bob',0),
(23432,'rob',1),
(32312,'dan',0),
(43432,'jan',1);

SELECT 
  EmployeeId,
  EmployeeName,
  Active,
  [Status] = CASE WHEN EmployeeId IS NOT NULL AND Active = 0 
     THEN 'Active' 
     ELSE 'Pending' END
FROM @e;

Results:

EmployeeId  EmployeeName  Active  Status
12312       bob           0       Active
23432       rob           1       Pending
32312       dan           0       Active
43432       jan           1       Pending

Upvotes: 0

BonyT
BonyT

Reputation: 10940

Select EmployeeId,EmployeeName,Active,
       case when EmployeeId is not null and Active = 0 then 'Active'  else 'Pending' end as Status
from EmployeeTable

if you need to set the @CurrentStatus variable then you'll need to do that separately:

Set @currentStatus = case when EmployeeId is not null and Active = 0 then 'Active'  else 'Pending' end 
from EmployeeTable

Upvotes: 0

Related Questions