Insomnia triumphs over all. Sleep schedule is whacked. Saw this
tweet from Jeff Atwood (
Blog|
Twitter) that referenced his post entitled "Why Can't Programmers.. Program?" located
here. I thought I would accept the FizzBuzz challenge and try and program something in Transact-SQL.
Following is my attempt developed and tested on SQL Server 2008 Dev Ed. This solution uses a recursive CTE (Common Table Expression). You can find tons more about recursive CTEs
here.
Please note that I know that CASE statement logic could be simplified - you can rely on WHEN expression order for evaluating which THEN clause is used for a given row. My preference is that with IF ELSE ELSE structures that the options be mutually exclusive.
;WITH cteCounter(number)
AS
(
SELECT 1 number
UNION ALL
SELECT (cteCounter.number + 1) number
FROM cteCounter
WHERE (cteCounter.number + 1) <= 100
)
-- Switch out the SELECT clauses to see basic recursive CTE behavior and possible FizzBuzz solution.
-- SELECT cteCounter.number
SELECT CASE WHEN ((cteCounter.number % 3 = 0) AND (cteCounter.number % 5 = 0)) THEN 'FizzBuzz'
WHEN ((cteCounter.number % 3 = 0) AND (cteCounter.number % 5 <> 0)) THEN 'Fizz'
WHEN ((cteCounter.number % 3 <> 0) AND (cteCounter.number % 5 = 0)) THEN 'Buzz'
ELSE CAST(cteCounter.number AS varchar(8)) END number
FROM cteCounter
ORDER BY cteCounter.number;
Dave Ballantyne (
Blog|
Twitter) publicly shamed me on Twitter for using a CTE to generate a range of number - thanks Dave <grin>! He rightly suggested using master.dbo.spt_values instead. I swore I would never use a CTE to implement a range of numbers! Following is the updated code.
SELECT CASE WHEN ((V.number % 3 = 0) AND (V.number % 5 = 0)) THEN 'FizzBuzz'
WHEN ((V.number % 3 = 0) AND (V.number % 5 <> 0)) THEN 'Fizz'
WHEN ((V.number % 3 <> 0) AND (V.number % 5 = 0)) THEN 'Buzz'
ELSE CAST(V.number AS varchar(8)) END number
FROM master.dbo.spt_values V
WHERE V.type = 'P'
AND V.number between 1 and 100
ORDER BY V.number;