Stumbled across Mike Hillwig’s post in response to a question posed by Brent Ozar. Depending on how literally you want to use the word “print” (do you mean the actual command, or do you just want output?), I think I have a slightly more elegant solution than what Mike proposed.
SELECT CASE WHEN n%3=0 AND n%5=0 THEN 'FizzBuzz' WHEN n % 3 = 0 THEN 'Fizz' WHEN n % 5 = 0 THEN 'Buzz' ELSE CONVERT(varchar(8), n) END as "N" FROM ( SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (ac1.Object_ID)) AS "N" FROM Master.sys.All_Columns ac1) x
Basically, it avoids the WHILE loop; however, if you must use a PRINT command, then Mike’s suggestion is probably as elegant as I can imagine. What’s interesting is that this may also provide me a better solution to my need to build a dynamic range that I posted a while back. I’ll have to think about it a bit more, but I think it’s possible.
