SQLTeam.com | Weblogs | Forums

How to use IF Condition in Where Clause


#1

Hi Friends,

Below IF statement in where clause not working please correct this.

I wants to select CID,CName from Category .IsDeleted value in the table should be 0 and @Status User input from code behind.

Category

CID CName ActiveDateTime IsDeleted
1 A 2015-06-16 23:33:16.653 0
2 B 2015-07-16 22:33:16.653 0
3 C 2015-07-03 12:00:16.653 1
4 D 2015-07-10 01:33:16.653 0
5 E 2015-07-02 01:33:16.653 0

Current DateTime - 2015-07-02 05:30:16.653

@Status int = 0;

SELECT CID,CName FROM Category WHERE IsDeleted = 0 AND IF @Status = -1 BEGIN
ActiveDateTime <> GETDATE() -- Expected result display all the five records IsDeleted should be 0
END ELSE IF @Status = 1 BEGIN
ActiveDateTime > GETDATE() -- Expected result display Active records only. IsDeleted should be 0 and ActiveDateTime greater than datetime now.
END ELSE @Status = 0 BEGIN
ActiveDateTime < GETDATE() -- Expected result display INActive record only.IsDeleted should be 0 and ActiveDateTime lesser than datetime now.
END


#2

Use the CASE statement


#3

Hi djj55,

Help me to use case for multiple possibilities of value which is not in table but user input (ex : @Status = -1 ,@Status = 1 ,@Status = 0 )


#4
SELECT CID
	,CName
FROM Category
WHERE IsDeleted = 0
	AND 1 = CASE @Status
		WHEN - 1
			AND ActiveDateTime <> GETDATE()
			THEN 1 -- Expected result display all the five records IsDeleted should be 0
		WHEN 1
			AND ActiveDateTime > GETDATE()
			THEN 1 -- Expected result display Active records only. IsDeleted should be 0 and ActiveDateTime greater than datetime now.
		WHEN 0
			AND ActiveDateTime < GETDATE()
			THEN 1 -- Expected result display INActive record only.IsDeleted should be 0 and ActiveDateTime lesser than datetime now.
		END

#5

Thank you gbritton.
It works fine..


#6

:relaxed: Great!