CODEWITHSUNDEEP
sqlsql-serversql-server-2008recursioncommon-table-expression
swordgit
swordgit

Reputation: 125

How can I increment a variable inside a CTE recursion with SQL Server 2008

I am having the following code, which I want to do a recursion with CTE and increment the variable inside so the recursion can use that to do a subquery.

WITH cte as
(
    select @layer as layers,
    case when exists(select * from #table where layer=@layer and string in ('abc','xyz)) then 10 else 0 end 
    union all
    select layers + 1, total
    from cte
    where layers + 1<4 -- 4 is a max number that is unknown and given by the user
)select * from cte

The #table has the following structure but the amount of data is dynamic

string     layer
abc        1
xyz        1
abc        2
xyz        2

So at layer 1, if it has either 'abc' or 'xyz' it will has a point of 10, same things happen for layer 2 until the the max layer given by the user. I want to get the point and the corresponding level from the recursion. While loop and cursor are forbidden. I am having trouble on incrementing the @layer in the recursion. Any suggestion? Thanks

Upvotes: 1

Views: 2486

Answers (1)

S3S
S3S

Reputation: 25132

I've never see a variable used in recursion, but I think you can do what you want with a tally table. 

if object_id('tempdb..#table') is not null drop table #table

create table #table (string varchar(64), layer int)
insert into #table
values
('abc',1),
('abc',2),
('xyz',2),
            --missing layer 3
            --missing layer 4
('fgh',5),  --not in the abc or xyz clause
('abc',6),
('xyz',7)   --greate than the max passed in via @layer




declare @layer int = 6

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select
    N as Layer
    ,case when max(layer) is not null then 10 else 0 end
from 
    cteTally
    full join #table 
    on N = layer and string in ('abc','xyz')
where N <= @layer
group by N
order by N

If you are really set on using recursion, which could be a lot slower if the @layer or max number passed in is large, then here is how you would accomplish that.

declare @layer int = 6

;with cte as(
    select 
        1 as layer
        ,total = case when exists(select * from #table t2 where t2.layer=layer and t2.string in ('abc','xyz')) then 10 else 0 end
    union all
    select 
        layer + 1
        ,total = case when exists(select * from #table t2 where t2.layer=c.layer + 1 and t2.string in ('abc','xyz')) then 10 else 0 end
    from cte c
    where layer < @layer)

select distinct 
    layer
    ,total = max(total)
from cte
group by layer
order by layer

Upvotes: 3

Related Questions