Skip to content
Advertisement

SQL query to use different tables conditionally

I have a stored procedure which uses different tables for a join based on an input parameter. Currently I have to write the SQL query twice (with only the table name difference). Is it possible to combine them so I do not have to repeat SQL query logic twice?

Current code:

CREATE PROCEDURE SampleProc
    @Condition BIT
AS
    IF @Condition = 0 
    BEGIN
        SELECT * 
        FROM TableA1 A /* Use TableA1 instead of TableA2 */ 
        INNER JOIN TableB B ON A.Id = B.Id /* The rest of the query remains the same */ 
        /* Inner Join some more complex join logic */
    END
    ELSE 
    BEGIN
        SELECT * 
        FROM TableA2 A /* Use TableA2 instead of TableA1 */ 
        INNER JOIN TableB B ON A.Id = B.Id  /* The rest of the query remains the same */ 
        /* Inner Join some more complex join logic */
    END
END

One of the possible ways is to store TableA1 / TableA2 data to a temp table first and use the temp table to join inside a complex query. Is there any better way?

Advertisement

Answer

If the two tables have the same structure (as implied by the temp table comment), you can do:

select . . .
from ((select a.* from tablea1 a where @condition = 0
      ) union all
      (select a.* from tablea2 a where @condition <> 0
      )
     ) a inner join
     b

Another alternative is dynamic SQL, but that can be tricky to maintain — because the code looks like a string.

Sometimes, you can do this with a left join as well:

select b.*, coalesce(a1.col, a2.col) as col
from b left join
     tablea1 a1
     on a1.id = b.id and @condition = 0 left join
     tablea2 a2
     on a2.id = b.id and @condition <> 0
where a1.id is not null or a2.id is not null
         . . . 

Although this should have good performance, it has the downside that all references to a columns need to use coalesce().

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement