CODEWITHSUNDEEP
sqlt-sqlsql-server-2016
developer
developer

Reputation: 1685

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?

Upvotes: 0

Views: 985

Answers (2)

Y.K.
Y.K.

Reputation: 692

if TableA1 and TableA2 have same columns, try this

        SELECT 
            * 
        From 
            (   select
                    *
                from
                    TableA1
                where
                    @Condition = 0
                union all
                select
                    *
                from
                    TableA2
                where
                    @Condition != 0) as A
        INNER JOIN 
            TableB B
        On 
            A.Id =B.Id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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().

Upvotes: 3

Related Questions