This reminds us how hard is is to keep your mind on SET BASED all the time.
Scott Turner
· 11 months ago
Rob,
This doesn't seem to scale well.
When I change the where clause to "WHERE Number < 102", I get:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
Scott Turner
· 11 months ago
Rob,
This also shows another thing. The CTE is recursive in nature. So what we really have here is an example of using recursion.
Rob Boek
· 11 months ago
That would be because the default MAXRECURSION is 100. If you want to go above that, add OPTION(MAXRECURSION 102) after the ORDER BY clause. You can use 0 for unlimited, but be careful.
Amit
· 9 months ago
declare @num int, @n1 int, @n2 int set @num = 1
while(@num < = 100) begin
select @n1 = (@num%3) select @n2 = (@num%5)
if (@n1 = 0) if (@n1 = 0 and @n2 = 0) Print 'FizzBuzz' else Print 'Fizz' if (@n1 != 0 and @n2 = 0) Print 'Buzz' else if (@n1 != 0 and @n2 != 0) Print @num set @num = @num + 1 end
All Comments
This doesn't seem to scale well.
When I change the where clause to "WHERE Number < 102", I get:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
This also shows another thing. The CTE is recursive in nature. So what we really have here is an example of using recursion.
@n1 int,
@n2 int
set @num = 1
while(@num < = 100)
begin
select @n1 = (@num%3)
select @n2 = (@num%5)
if (@n1 = 0)
if (@n1 = 0 and @n2 = 0)
Print 'FizzBuzz'
else
Print 'Fizz'
if (@n1 != 0 and @n2 = 0)
Print 'Buzz'
else
if (@n1 != 0 and @n2 != 0)
Print @num
set @num = @num + 1
end