Select iif sql multiple conditions in one result

Question

We have multiple variables: Variable1, Variable2, and Variable3 in SQL Server 2012

If one of these variables fail, we out fail status

Declare @Variable1 int
Set @Variable1 = ( select  top 1 count(*)
FROM Table1
where condition1=1 
group by column1,column2
having count(1) >1)

Declare @Variable2 int
Set @Variable2 = ( select  top 1 count(*)
FROM Table1
where condition2=1 
group by column3,column4
having count(1) >1)

Declare @Variable3 int
Set @Variable3 = ( select  top 1 count(*)
FROM Table1
where condition3=1 
group by column5,column6
having count(1) >1)

SELECT IIF ( @Variable1 >=1 and Variable2 >=1 and Variable3 >=1, 'Fail', 'Pass' ) AS ResultTable1;

The ResultTable1 always get result base on @Variable1 and not base on @Variable2 and @Variable3 result

Can any one help? -thanks

Answer 1

If one of these variables fail, we out fail status

In that case, you need to use OR in your IIF(), not AND. The way you have it now, ALL of the variables have to fail to have a failed status.

SELECT IIF ( @Variable1 >=1 OR @Variable2 >=1 OR @Variable3 >=1, 'Fail', 'Pass' ) AS ResultTable1