6
\$\begingroup\$

I've written a simple FizzBuzz in TSQL using some IF loops.

Here's a SEDE link to run it in your browser.

DECLARE @i int = 1
DECLARE @str varchar(8) = '';
WHILE @i <= 30 BEGIN
    SET @str = ''
    IF @i % 3 = 0
    BEGIN
        SET @str = 'FIZZ'
    END
    IF @i % 5 = 0
    BEGIN
        SET @str = @str + 'BUZZ'
    END
    PRINT str(@i) + ': ' + @str
    SET @i = @i + 1
END

I wasn't sure the best practice with the IF loops.

\$\endgroup\$
  • 1
    \$\begingroup\$ IF is not a loop \$\endgroup\$ – Gentian Kasa Dec 3 '15 at 9:07
7
\$\begingroup\$

In SQL (any flavor) you usually want to avoid loops, if at all possible, in favor of set-based operations. However, in your case, there's not really a set, so-to-speak, other than a series of ints 1 to 30. Also, it's quite unusual in any SQL to use the console for anything besides routine messages like how many rows were affected by a query.

Still, just for the sake of learning, let's go ahead and make a (temporary) data set, @FizzBuzzNumbers, and put the numbers in it.

DECLARE @FizzBuzzNumbers TABLE (number INT);
DECLARE @i INT = 1, @max INT = 30;
WHILE @i <= @max
BEGIN
    INSERT INTO @FizzBuzzNumbers (number) VALUES (@i);
    SET @i = @i+1;
END

Then we can select from that set and apply the FizzBuzz using case. (note that converting the number to a string is needed, since a column/field can only have one type)

There is no need to print the results either, since when you select them they will be shown in output automatically.

DECLARE @Fizz INT = 3, @Buzz INT = 5;
SELECT
    CASE
        WHEN (number % @Fizz = 0) and (number % @Buzz = 0) THEN 'FizzBuzz'
        WHEN (number % @Fizz = 0) THEN 'Fizz'
        WHEN (number % @Buzz = 0) THEN 'Buzz'
        ELSE CONVERT(VARCHAR(8), number)
    END AS [FizzBuzz Results]
FROM @FizzBuzzNumbers;

Demo on SEDE


Now if you insisted on keeping the loop, which is more familiar to traditional programmers (but not idiomatic SQL, or "SQLic") you should still go with the faster case instead of if, as it is faster but also reads a lot easier.

case statements in SQL are much more flexible in the conditions you can make them try to match than what you would expect in most traditional languages. (on the other hand, the logic on the other side of the case, as in, what to do when a case is matched, is extremely simplistic, so you often need if/else type logic for more complex things).

\$\endgroup\$
  • 1
    \$\begingroup\$ You have some valid points in here, but you miss the general approach which allows for extending this as you aren't joining the Fizz and the Buzz and only calculating them once. \$\endgroup\$ – holroy Dec 7 '15 at 20:38
4
\$\begingroup\$

Here's a pretty succinct way to do it. I use a recursive common-table expression to fill in a table of integers from 1 to 100.

with tbl (idx)
as
(
    select 1
    union all
    select idx + 1 from tbl where idx < 100
)
select
    case 
        when idx % 15 = 0 then 'fizzbuzz'
        when idx % 3 = 0 then 'fizz'
        when idx % 5 = 0 then 'buzz'
        else cast(idx as varchar(10))
    end
from tbl

Here's an updated version that only runs the two modulus calculations once and uses string concatenation:

with tbl (idx) as
(
    select 1
    union all
    select idx + 1 from tbl where idx < 100
),
tbl2 (idx, isFizz, isBuzz) as
(
    select idx, iif(idx % 3 = 0, 'Fizz', ''), iif(idx % 5 = 0, 'Buzz', '')
    from tbl
)
select 
    iif( 
        len(isFizz) > 0 or len(isBuzz) > 0, 
        isFizz + isBuzz, 
        cast(idx as varchar(10)) 
    ) as result
from tbl2

The first table uses a recursive CTE to generate a table with 100 rows of integers in it. The second table adds two columns that calculate the Fizz and Buzz values. The final select puts everything together using the lengths of the Fizz and Buzz columns as a guide.


Here's I think a better solution than my second solution. It uses a table filled with 3s and a table filled with 5s which together obviate the need for modulus calculations. I think that since, as the other answerer mentioned, we should be using set-based operations, this is a better solution. In the final select, I use a call to coalesce with a string concatenation to put everything together.

with t3 (idx, word) as
(
    select 3, 'Fizz'
    union all
    select idx + 3, word from t3 where idx < 100
),
t5 (idx, word) as
(
    select 5, 'Buzz'
    union all
    select idx + 5, word from t5 where idx < 100
),
t0 (idx) as
(
    select 1
    union all
    select idx + 1 from t0 where idx < 100
)
select coalesce(t3.word + t5.word, t3.word, t5.word, cast(t0.idx as varchar(10)))
from t0
left outer join t3 on t3.idx = t0.idx
left outer join t5 on t5.idx = t0.idx
order by t0.idx

The further out I abstract some of this, the uglier my code gets. I still prefer my first solution.

\$\endgroup\$
  • 1
    \$\begingroup\$ I miss the general approach which allows for extending this as you aren't joining the Fizz and the Buzz and only calculating them once. \$\endgroup\$ – holroy Dec 7 '15 at 20:52
  • 1
    \$\begingroup\$ @holroy, I updated my answer to provide a solution that I think addresses your concerns. I think you're missing the point however. The FizzBuzz question is not meant to judge a candidate's coding design abilities. Answers are not meant to follow SOLID principles, or DRY or YAGNI or whatever. It's meant to make sure the candidate can write code. That's it. \$\endgroup\$ – user2023861 Dec 7 '15 at 22:16
  • \$\begingroup\$ re "It's meant to make sure the candidate can write code" : Our main use of FizzBuzz is to test candidates about how they code (and we mainly wait a unit test at end), so I would not be so categorical about not used to test design/coder practices . (btw, interesting approaches) \$\endgroup\$ – Tensibai Dec 8 '15 at 14:35
  • \$\begingroup\$ @Tensibai, would you only be happy if a solution looked like this gist.github.com/stuhacking/1259421 ? I 100% disagree. In my interviewing experience, I've found that the simple FizzBuzz question does actually weed out candidates. Why? Maybe they can't code, or they aren't prepared, or they jump to conclusions. FizzBuzz shines a light on these kinds of candidates. The problem with using it to judge a candidate's design skills is that FizzBuzz is too well-known. It's better to ask something that forces the candidate to think. \$\endgroup\$ – user2023861 Dec 8 '15 at 14:47
  • \$\begingroup\$ @user2023861 I don't get where you go with this link, but no it's not what I expect. I just meant I would not be categorical about its use. Of course it can't be the only point to judge someone coding practices exactly because it is well known. We use it in interview, not as pre-selection case, along others things to have an idea on how the candidate think about a problem and start coding. \$\endgroup\$ – Tensibai Dec 8 '15 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.