Conditional select query

I have a large SQL query with multiple statements and UNION ALL. I am doing something like this now:

DECLARE @condition BIT;
SET @condition = 0;

SELECT * FROM table1
WHERE @condition = 1;

UNION ALL

SELECT * FROM table2

In this case, table1 won't return any results. However, that query is complex with many joins (suc开发者_C百科h as FullTextTable). The execution plan's estimate shows a high cost, but the actual number of rows and time to execute seems to show otherwise. Is this the most efficient way of filtering a whole query, or is there a better way? I don't want anything in the first select to run, if possible.

I would imagine that your eventual SQL query with all of the unions and conditions that depend on pre-calculated values gets pretty complicated. If you're interested in reducing the complexity of the query (not to the machine but for maintenance purposes), I would go with a moving the individual queries into views or table valued functions to move that logic elsewhere. Then you can use the if @condition = 1 syntax that has been suggested elsewhere.

The best way to solve this is by using Dynamic SQL. The problem with DForck's solutions is that it may lead to parameter sniffing. Just to give a rough idea, your query might look something like this

DECLARE @query VARCHAR(MAX);

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

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

sp_executesql @query

This is just a simplified case, but in actual implementation you would parameterize the dynamic query which will solve the problem of parameter sniffing. Here is an excellent explanation about this problem Parameter Sniffing (or Spoofing) in SQL Server

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