CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
AR_Dev
AR_Dev

Reputation: 85

Attaching conditional AND condition in SQL Server?

I want to have an AND condition attached to my WHERE clause based on some condition. Below is the situation I am facing

DECLARE
    @param1 INT, @param2 INT

SET @param1 = 1
SET @param2 = 1

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.a = t2.b
INNER JOIN TABLE Table3 t3 ON t3.c = t2.d
WHERE t1.id = 1
  AND t2.id = 2

Here is where i want the condition to come in--

Case when `@param1 = 1` and `@param2 = 1` THEN `AND t3.id = 4`

But I guess and does not work like this ? :/ Any workaround for this?

Upvotes: 0

Views: 86

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

I would write this as:

where t1.id = 1 and t2.id = 2 and
      ((@param1 = 1 and @param2 = 1 and t3.id = 4) or
       not (@param1 = 1 and @param2 = 1)
      )

This does assume that the parameters are never NULL.

Upvotes: 1

S3S
S3S

Reputation: 25132

Just add this to your where clause

and isnull(t3.id,'') = case when @param1 = 1 and @param2 = 1 then 4 else isnull(t3.id,'') end

Upvotes: 2

Siyual
Siyual

Reputation: 16917

You can add the following as an AND condition to check if t3.id = 4 when @param1 and @param2 both equal 1, or no additional condition if they don't:

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.a = t2.b
INNER JOIN Table3 t3 ON t3.c = t2.d
WHERE 
    t1.id = 1
    AND t2.id = 2
And
(
    (
        @param1 = 1
    And @param2 = 1
    And t3.id = 4
    )
    Or
    (
        @param1 <> 1
    Or  @param2 <> 1
    )
)

Upvotes: 0

Related Questions