Conditional Selection Request

Question

I have a big SQL query with several and operators UNION ALL. Now I am doing something like this:

DECLARE @condition BIT;
SET @condition = 0;

SELECT * FROM table1
WHERE @condition = 1;

UNION ALL

SELECT * FROM table2

In this case, table1 will not return any results. However, this query is complex with many joins (e.g., FullTextTable). Evaluation of the execution plan shows a high cost, but the actual number of lines and the execution time seem to show different. Is this the most efficient way to filter the entire query, or is there a better way? I do not want anything in the first choice, if possible.

Answer 1

- Dynamic SQL. DForck , sniffing . , .

DECLARE @query VARCHAR (MAX);

IF (@condition = 0) SET @query = 'SELECT * FROM table1               UNION ALL '

SET @query = @query + 'SELECT * FROM table2'

sp_executesql @query

, , sniffing . Sniffing ( Spoofing) SQL Server

Answer 2

, SQL- , , . ( , ), . if @condition = 1, .

Answer 3

I think you might be better off with this:

if (@condition=1)
begin

select * from table1
union all
select * from table2

end
else
begin

select * from table2

end