How to use IF Condition in Where Clause

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

Use the CASE statement

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 )

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
1 Like

Thank you gbritton.
It works fine..

:relaxed: Great!