I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.
Option 1 :
/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Option 2:
/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.
Reference : Pinal Dave (http://blog.sqlauthority.com)
pinaldave
Thanx for sharing your knowledge.
I have question. I want to get information from a simple table but optional WHERE clause, but using CTE (for some purpose) How to accomplish my goal but with using string for query ???
@Aasim,
To accomplish CTE with optional parameter, you can use is as this way:
;WITH cte1 AS
( SELECT Col1
FROM Table
WHERE (@Param IS NULL OR Col1 = @Param)
),
cte2 AS (
SELECT Col2
FROM Table
WHERE (@Param IS NULL OR Col2 = @Param)
)
SELECT cte1.Col1,cte2.Col2
FROM cte1
CROSS JOIN cte2
GO
Here, if @param has value, then it will filter result in CTE.
Let me know if it helps you.
Thanks,
Tejas
@aasim
WITH CTE(String) AS (SELECT ‘a string’)
SELECT String FROM CTE;