Sql 2005 Case Clause Query

Question

DECLARE @Status INT 
SET @Status = 1 -- (can be 0,1,2)

SELECT * FROM Members 
WHERE Mem_Number in (
 SELECT MemberID 
 FROM GroupMember 
 WHERE GroupId = @GpId 
 AND IsCardNumber = 0) 
AND 
CASE WHEN @Status < 2 THEN Mem_Archeived = @Status
ELSE 1=1
END

--In this If @status variable is less than 2 then Mem_Archeived column is considered otherwise not.

Any help on this would be great!

Thanks Kapil

Answer 1

0

I think that should work

DECLARE @Status INT  
SET @Status = 1 -- (can be 0,1,2)

SELECT * FROM Members 
WHERE Mem_Number in (
 SELECT MemberID 
 FROM GroupMember 
 WHERE GroupId = @GpId 
 AND IsCardNumber = 0) 
AND 
Mem_Archeived = CASE WHEN @Status < 2 THEN @Status ELSE Mem_Archeived END
END

Scott

answered Feb 25 '10 at 11:34 AM

Scott Fisher
46

(comments are locked)

Answer 2

0

-- Try this

DECLARE @Status INT SET @Status = 1 -- (can be 0,1,2)

SELECT * FROM Members WHERE Mem_Number in ( SELECT MemberID
FROM GroupMember
WHERE GroupId = @GpId
AND IsCardNumber = 0) AND (Mem_Archeived = @Status OR @Status < 2)

answered Mar 17 '10 at 03:01 AM

Aditya Kumar
1

(comments are locked)

Sql 2005 Case Clause Query

Follow this question

Related Questions