Zahid Zahid - 4 months ago 15
SQL Question

Write Query without using loop

Write a program using SQL that prints the numbers from 1 to 100.

But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”

DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ''
IF @counter % 3 = 0
SET @output = 'Fizz'
IF @counter % 5 = 0
SET @output = @output + 'Buzz'
IF @output = ''
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END


This gives desired output. But,I am asked not to use loop,Is it possible to do this any other way? How about using CTE?

Answer
;With cte(n)--this is a recursive cte
as
(
select  1--anchor part
union all
select n+1
from cte where n<100 --recursive part,when this completes we have a temp table of 100 numbers
)
select 
case when n%3=0 and n%5=0 then 'Fizz Buzz'
     when n%5=0 then 'Buzz'
     when n%3=0 then 'Fiz'
     else cast(n as varchar(4)) end
from cte

From that table ,we are using case to calculate modulo.Read out below articles on why numbers tables are usefull and how they can replace loops..

1.http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx 2.http://www.sqlservercentral.com/articles/T-SQL/62867/
3.http://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable