Sql intersect conditionally

Question

I want to know if we can make the intersection conditional. theres is a somes request, but the result is incorrect (always empty). I write what should happen.

DECLARE @CAN_USE_TABLE1 BIT DECLARE @CAN_USE_TABLE2 BIT DECLARE @CAN_USE_TABLE3 BIT DECLARE @CAN_USE_TABLE4 BIT DECLARE @TABLE1 AS TABLE ( ABC INT ) -- values will be 1,2,3 DECLARE @TABLE2 AS TABLE ( ABC INT ) -- values will be 1,2 DECLARE @TABLE3 AS TABLE ( ABC INT ) --EMPTY TABLE DECLARE @TABLE4 AS TABLE ( ABC INT ) --EMPTY TABLE INSERT INTO @TABLE1 VALUES (1) INSERT INTO @TABLE1 VALUES (2) INSERT INTO @TABLE1 VALUES (3) INSERT INTO @TABLE2 VALUES (1) INSERT INTO @TABLE2 VALUES (2) SET @CAN_USE_TABLE1 = 1 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 1 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- NO RESULT -- -- BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 AND TABLE3 SHOULD BE INTERSECTED. AND BECAUSE TABLE3 IS EMPTY, THE RESULT IS EMPTY. SET @CAN_USE_TABLE1 = 1 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 0 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- 1 -- 2 -- -- BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 SHOULD BE INTERSECTED SET @CAN_USE_TABLE1 = 0 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 0 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- 1 -- 2 -- -- BECAUSE, AT THIS STAGE, ONLY TABLE 2 SHOULD BE USED 

Answer 1

An empty set INTERSECT ed with any other set will always be empty. It's like multiplying by 0. You always get 0.

Conditional INTERSECT ing will require either a dynamic query or an intermediate table, for example:

Initialization

 DECLARE @CAN_USE_TABLE1 BIT DECLARE @CAN_USE_TABLE2 BIT DECLARE @CAN_USE_TABLE3 BIT DECLARE @CAN_USE_TABLE4 BIT DECLARE @TABLE1 AS TABLE ( ABC INT ) DECLARE @TABLE2 AS TABLE ( ABC INT ) DECLARE @TABLE3 AS TABLE ( ABC INT ) DECLARE @TABLE4 AS TABLE ( ABC INT ) DECLARE @RESULT AS TABLE ( ABC INT ) --Adding this result table INSERT INTO @TABLE1 VALUES (1) INSERT INTO @TABLE1 VALUES (2) INSERT INTO @TABLE1 VALUES (3) INSERT INTO @TABLE2 VALUES (1) INSERT INTO @TABLE2 VALUES (2) SET @CAN_USE_TABLE1 = 1 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 0 SET @CAN_USE_TABLE4 = 0 

Treatment

 INSERT INTO @RESULT SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1=1 UNION SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2=1 UNION SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3=1 UNION SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4=1 DELETE r FROM @RESULT r WHERE NOT EXISTS(SELECT 1 FROM @TABLE1 WHERE ABC=r.ABC) AND @CAN_USE_TABLE1=1; DELETE r FROM @RESULT r WHERE NOT EXISTS(SELECT 1 FROM @TABLE2 WHERE ABC=r.ABC) AND @CAN_USE_TABLE2=1; DELETE r FROM @RESULT r WHERE NOT EXISTS(SELECT 1 FROM @TABLE3 WHERE ABC=r.ABC) AND @CAN_USE_TABLE3=1; DELETE r FROM @RESULT r WHERE NOT EXISTS(SELECT 1 FROM @TABLE4 WHERE ABC=r.ABC) AND @CAN_USE_TABLE4=1; SELECT * FROM @RESULT; 

Result

 1 2 

Answer 2

 declare @sql nvarchar(4000), @params nvarchar(4000) if @can_use_table1 = 1 select @sql= 'select abc from @table1' if @can_use_table2 = 1 begin if @can_use_table1 = 1 select @sql = @sql + ' intersect ' select @sql = @sql + 'select abc from @table2' end if @can_use_table3 = 1 begin if @can_use_table1 = 1 or @can_use_table2 = 1 select @sql= @sql + ' intersect ' select @sql= @sql + 'select abc from @table3' end if @can_use_table4 = 1 begin if @can_use_table1 = 1 or @can_use_table2 = 1 or @can_use_table3 = 1 select @sql= @sql + ' intersect ' select @sql= @sql + 'select abc from @table4' end select @params ='@can_use_table1 bit, @can_use_table2 bit, @can_use_table3 bit, @can_use_table4 bit, @table1 table, @table2 table, @table3 table, @table4 table' exec sp_executesql @sql,@params, @can_use_table1,@can_use_table2, @can_use_table3, @can_use_table4, @table1, @table2, @table3, @table4 

Answer 3

You can do this in one request like:

 select ABC from (SELECT ABC, @CAN_USE_TABLE1 as CanUse1, 0 as CanUse2, 0 as CanUse3, 0 as CanUse4 FROM @TABLE1 union all SELECT ABC, 0, @CAN_USE_TABLE2, 0, 0 FROM @TABLE2 union all SELECT ABC, 0, 0, @CAN_USE_TABLE3, 0 FROM @TABLE3 union all SELECT ABC, 0, 0, 0, @CAN_USE_TABLE4 FROM @TABLE4 ) t group by ABC having max(Canuse1) = @CAN_USE_TABLE1 AND max(CanUse2) = @CAN_USE_TABLE2 AND max(CanUse3) = @CAN_USE_TABLE3 And max(CanUse4) = @CAN_USE_TABLE4