How seditious to exclude requests connected through UNION by parameter value?

Question

SQL

Tell me, is it bad practice to use such constructions?

DECLARE @P INT = 1;

SELECT * 
FROM (
    SELECT TOP(1000) id FROM Table1 WHERE @P = 1
    
        UNION ALL
    
    SELECT TOP(10000) id FROM Table2 WHERE @P = 2
    
        UNION ALL

    SELECT TOP(100000) id FROM Table3 WHERE @P = 3
) AS T

How badly does this practice affect the query execution plan?
Should I create similar table-valued functions or use it in EXEC with parameterization?
From the point of view of the plan, it looks reasonable (except for "it was eliminated in runtime").
Could this negatively affect the evaluation of a request using a similar function?

PS I'm sure the case is frequent, but I didn't find any articles on this topic.

Answer 1

From the point of view of the query execution plan, nothing bad will happen. the engine itself will discard the scan of tables, the reading of which is not required to build the result. which he himself admitted.
in terms of code beauty and support, decide for yourself. if as a result the records will always be from the same table, then you can separate the selections with logic in multi-statement table-valued functions, as an option. or run a dynamically generated query against a table with the desired name.