Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

if else within CTE?

I want to execute select statement within CTE based on a codition. something like below

;with CTE_AorB
(
  if(condition)
    select * from table_A
   else
    select * from table_B
),
CTE_C as
(
   select * from CTE_AorB // processing is removed
)

But i get error on this. Is it possible to have if else within CTEs? If not is there a work around Or a better approach.

Thanks.

like image 756
stackoverflowuser Avatar asked May 25 '10 18:05

stackoverflowuser


People also ask

Can we use if else in CTE?

If you have used a CTE in your query, this can sometimes be tricky as they cannot be used in a conditional like IF EXISTS (queryWithCTE).

How do you write if condition in CTE?

You can use SELECT * INTO #TempTable FROM CTE immediately after WITH CTE AS ( ) and then use IF .... ELSE .... which can column values from #TempTable for conditions in IF , ELSE.

Which clause Cannot be used in CTE?

The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, FOR BROWSE, cannot be used in the CTE query definition. "SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member.

Is it better to use CTE or subquery?

Advantage of Using CTE CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.


1 Answers

try:

;with CTE_AorB
(
    select * from table_A WHERE (condition true)
    union all
    select * from table_B WHERE NOT (condition true)
),
CTE_C as
(
   select * from CTE_AorB // processing is removed
)

the key with a dynamic search condition is to make sure an index is used, Here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

if you are on the proper version of SQL Server 2008, there is an additional technique that can be used, see: Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

If you are on that proper release of SQL Server 2008, you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

and an index can be used (if you have one defined on Column2)

like image 72
KM. Avatar answered Oct 09 '22 17:10

KM.