FizzBuzz in T-SQL

February 3, 2009

Pinal Dave posted yesterday about how to solve the Fizz Buzz problem using T-SQL.

Definition of FizzBuzz Puzzle : Write a program 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”.

His solution works, but he is using procedural logic. Some of the biggest causes of performance problems in SQL Server are caused by application developers who try to use procedural logic instead of using the set-based logic that databases are meant for.

Here is how to solve the FizzBuzz problem using set-based logic in T-SQL:

WITH Numbers(Number) AS (
  SELECT 1
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number < 100
)
SELECT
  CASE 
    WHEN Number % 3 = 0 AND Number % 5 = 0 THEN 'FizBuzz'
    WHEN Number % 3 = 0 THEN 'Fizz'
    WHEN Number % 5 = 0 THEN 'Buzz'
    ELSE CONVERT(VARCHAR(3), Number)
  END
FROM Numbers
ORDER BY Number

Tagged as: , , ,

  • Amit
    if (@n1 != 0 and @n2 != 0) not required though
  • Amit
    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
  • 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.
  • Scott Turner
    Rob,

    This also shows another thing. The CTE is recursive in nature. So what we really have here is an example of using recursion.
  • Scott Turner
    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."
  • This reminds us how hard is is to keep your mind on SET BASED all the time.
  • Nice
blog comments powered by Disqus