Any SQL where clause performance similar to where 1=0
You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL.

To demonstrate, here is a simplified version based on the AdventureWorks sample database:

The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of the UseArchive column in a Parameter table:

CREATE TABLE dbo.Parameter
(
    ProductID integer NOT NULL PRIMARY KEY,
    UseArchive bit NOT NULL
);

INSERT dbo.Parameter
    (ProductID, UseArchive)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 0);


This example specifies that rows should come from TransactionHistory for products 1, 2, and 4. For product 3, rows should come from TransactionHistoryArchive.

Solution:

SELECT 
    P.Name, 
    CA.TransactionID
FROM Production.Product AS P
JOIN dbo.Parameter AS PTR
    ON PTR.ProductID = P.ProductID
CROSS APPLY
(
    SELECT TH.ProductID, TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE 
        TH.ProductID = P.ProductID
        AND PTR.UseArchive = 0

    UNION ALL

    SELECT THA.ProductID, THA.TransactionID
    FROM Production.TransactionHistoryArchive AS THA
    WHERE 
        THA.ProductID = P.ProductID
        AND PTR.UseArchive = 1
) AS CA;


The execution plan for this query is:



The key here is the outer reference PTR.UseArchive = value inside the apply. This allows the optimizer to construct a plan featuring Filters with Start-Up Expressions. For example, the Filter above the TransactionHistory table Index Seek has:



The Filter Predicate above the other seek is very similar, of course, but tests UseArchive = 1.

Each Filter Predicate is evaluated once per product. It determines whether the seek below it in the plan will be executed on that iteration of the nested loop join.

In this example, the effect is that the TransactionHistory index seek is executed three times; the seek on the TransactionHistoryArchive table is executed just once.

You may be able to implement this pattern for your use case.