FizzBuzz in T-SQL

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.

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).

StackExchange Code Review Q#112707, answer score: 7