How to Use If Statement After a Cte (SQL Server 2005)

How can I use if statement after a CTE (SQL Server 2005)

Common table expressions are defined within the context of a single statement:

WITH cte_name AS (
  <cte definition>)
<statement that uses cte>;

So you can do something like:

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
SELECT * FROM CTE;

or

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
UPDATE CTE 
SET somefield = somevalue
WHERE id = somekey;

A CTE must be followed by a single
SELECT, INSERT, UPDATE, MERGE, or
DELETE statement that references some
or all the CTE columns. A CTE can also
be specified in a CREATE VIEW
statement as part of the defining
SELECT statement of the view

if else within CTE?

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)

Scope of an CTE in SQL Server 2005

The CTE is part of the subsequent statement only.

The subsequent statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION, INTERSECT etc)

For example:

;WITH cte1 AS
(
   select ...
), cte2 AS
(
    select ...
)
SELECT ...
UNION 
SELECT ...;

The rule of thumb is that the scope is until where next ; would be. A semi-colon terminates any statement but is optional unfortunately.

Your failing code above is actually this

...;
WITH emp_CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS IdentityId, *
    FROM dbo.employee )
SELECT * FROM EMPLOYEES; 
SELECT * FROM emp_CTE;

So the CTE is only in scope up until ...EMPLOYEES;

SQL-Server: Incorrect syntax near the keyword 'with'. If this statement is a common table expression

Add some semicolons:

create table #temp
(
  pName Varchar(20),
  DateBegin DateTime,
  DateEnd DateTime
)

Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')

Insert Into #temp(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012');

With DateRange(dt) As
(
    Select Convert(Datetime, '01/01/2012')
    UNion All
    Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)

Select T.pName, Dt.dt from #temp T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd;

Drop Table #temp

http://sqlfiddle.com/#!6/06e89

Recursive Query using CTE in SQL Server 2005

edit OK having actually read the requirements and thought a bit this is actually quite easy (I think!)

The point is that we want two things: the category hierarchy, and a count of products. The hierarchy is done by a recursive CTE, and counting is done outside that:

-- The CTE returns the cat hierarchy:
-- one row for each ancestor-descendant relationship
-- (including the self-relationship for each category)
WITH CategoryHierarchy AS (
    -- Anchor member: self relationship for each category
    SELECT CategoryID AS Ancestor, CategoryID AS Descendant
    FROM Categories
UNION ALL
    -- Recursive member: for each row, select the children
    SELECT ParentCategory.Ancestor, Children.CategoryID
    FROM 
        CategoryHierarchy AS ParentCategory
        INNER JOIN Categories_XREF AS Children
        ON ParentCategory.Descendant = Children.ParentID
)
SELECT CH.Ancestor, COUNT(ProductID) AS ProductsInTree
-- outer join to product-categories to include 
-- all categories, even those with no products directly associated
FROM CategoryHierarchy CH
LEFT JOIN Products_Categories_XREF PC
ON CH.Descendant = PC.CategoryID
GROUP BY CH.Ancestor

The results are:

Ancestor    ProductsInTree
----------- --------------
942         13
943         13
959         9
960         1
961         3

I am indebted to this article by the inestimable Itzik Ben-Gan for getting my thinking kick-started. His book 'Inside MS SQL Server 2005: T-SQL Querying' is highly recommended.

How to join the result set of Common Table Expression with other existing table in sql server 2005?

You can define multiple CTEs for a single select, and each CTE can reference previously defined ones. So you can do:

With CTEQuery
as
(SELECT     StudentOnlineExamCourseAnswer.StudentID, StudentOnlineExamCourseAnswer.OnlineExamID, StudentOnlineExamCourseAnswer.CourseID, 
                      StudentOnlineExamCourseAnswer.CentreID,
case QuestionBank.ComplexLevelID when 1 then (2) when 2 then (4) when 3 then (6) when 4 then (8) when 5 then (10) end as Mark
FROM         QuestionBank INNER JOIN
                      StudentOnlineExamCourseAnswer ON QuestionBank.Answer = StudentOnlineExamCourseAnswer.Answer AND 
                      QuestionBank.QuestionID = StudentOnlineExamCourseAnswer.QuestionID)
, SummarizedCTE as ( 
select StudentID, OnlineExamID ,CourseID , CentreID , sum(Mark) as TotalMark from CTEQuery 
group by StudentID, OnlineExamID ,CourseID , CentreID)
select <new query involving joining SummarizedCTE with the "other table" referenced in your discussion>

SQL Select: Using CTE as dataset for IN clause - syntax error

WITH    CTEName as
        (
        -- CTE syntax goes here
        )
SELECT  *
FROM    mytable
WHERE   myfield IN 
        (
        SELECT  ctefield
        FROM    CTEName
        )
GROUP BY
        myotherfield

In other words, the CTE should be defined before all other commands (as if they were actual tables).

Also note that the semicolon (;) is normally not required. However, the CTE syntax was implemented in SQL Server after it had already introduced the keyword WITH for its own purposes, so implicit statement breaking does not work with CTE anymore:

SELECT  *
FROM    mytable
WITH    q AS (SELECT 1)
SELECT  *
FROM    q

It's hard to define where the WITH is used in the first or second statement here.

So it is considered a best practice to always prepend WITH with a semicolon so that you could easily cut and paste it anywhere in your code without having to worry about whether it is a first statement in a batch or not.

---

Related Topics