CTE

DROP TABLE #TEMP_PRODUCTS

CREATE TABLE #TEMP_PRODUCTS
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_PRODUCTS
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75

/*Simple CTE Query*/
;with cte_products /*CTE Table Name*/
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products

/*Simple CTE Query with column names*/
;with cte_products (prod_desc,price,quantity) /*Column name*/
as
(
select PRODUCT_NAME,PRODUCT_PRICE,PRODUCT_QUANTIY from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select prod_desc,price,quantity from cte_products where price >2000 /*Same Column Name Should be used*/


/*Note: You need to select the CTE table immediatly after defining (scope will lost after that line): Below will produce error*/

;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select 1
select * from cte_products
/*
Msg 422, Level 16, State 4, Line 8
Common table expression defined but not used.
**/

/*So the Query should be:*/

;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products
select 1

/*But you can use CTE tables multible times with joins and unions*/
;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products
union all
select * from cte_products

/*u can delete,update and insert operations in CTE table: record will be deleted in #TEMP_PRODUCTS*/

begin tran
;with cte_products
as
(
select * from #TEMP_PRODUCTS
)
delete from cte_products where product_id >= 3

select * from #TEMP_PRODUCTS
rollback

/****************************************************************/

/*Recursive CTE Query*/

/*using CTE tables with in CTE makes recursive*/

/*For Example, we need to split this into each row using comma seperator*/

--Consider this string 'MOOV,ANAND,VICKY,BS,AS,KC'

/*We can seperate the names by finding position of comma
For example, 1st comma position is 5
so,

select substring('MOOV,',1,5-1)

2nd comma position is 6
so,

select substring('ANAND,',5+1,12-1)

so we need postion like this format
start end
1 5
6 11
12 17

*/

DECLARE @String VARCHAR(1000)
SET @String = 'MOOV,ANAND,VICKY,BS,AS,KC'
SET @String = @String + ','

;with cte_recursive(Start,[End])
as
(

SELECT 1 as 'Start',CHARINDEX(',',@String,1) as 'End'
union all
SELECT [End]+1 as 'Start',CHARINDEX(',',@String,1+[end]) as 'End' from cte_recursive where [end] < len(@String) /*Using cte table inside cte expression*/
)
select start,[end],@String,SUBSTRING(@String,Start,[End]-Start) as Result from cte_recursive


/*
Important note: Recursive property will call the same CTE table again and again.. So use it in right place of ur project
Just study the above query well deeper.. We can use CTE recursive in many places. It will avoid while loops and complex querying;

CTE and derived tables are moreover same!.. Recursive is the top feature in CTE.. So, Look deeper in Recursive CTE...
*/