I decided to try it out in TSQL. Once I had a solution (it took about five minutes, it would have been about two but I kept getting syntax errors on the CASE statement). I had so much fun that I sent it out to our team to see who else could meet the requirements and I added one more: no cursors are allowed
Before you read any further, try it out for yourself. It's not hard. A solution should take you less than 10 minutes.
Here are the various solutions that we came up with on our team.
First up, mine:
DECLARE @i VARCHAR(3) ;
DECLARE @s VARCHAR(8) ;
SET @i = 1 ;
WHILE @i < 101
BEGIN
SELECT @s = CASE WHEN ( ( @i % 3 = 0 )
AND ( @i % 5 = 0 )
) THEN 'BizzBuzz'
WHEN ( @i % 3 = 0 ) THEN 'Bizz'
WHEN ( @i % 5 = 0 ) THEN 'Buzz'
ELSE @i
END ;
PRINT @s ;
SET @i = @i + 1 ;
END ;
I didn't need to use the @s variable for the print statements, but overall, first pass, it was simple and worked fine. I'm assuming I'd still get interviewed although my pay scale may have dropped a grade because I introduced parameters that weren't really necessary.
The next submission came in from Chris:
declare @i int
set @i = 1
while( @i < 101)
begin
if @i%3 = 0 print 'Bizz'
else if @i%5 = 0 print 'Buzz'
else if @i%3 = 0 and @i%5 = 0 print 'BizzBuzz'
else print @i
set @i = @i+1
end
He fixed my problem with the string, but he introduced a new one. Go back, reread the code and see if you can spot it. Give up? Because he checked for 3 then 5 then the combination, none of his code found the combination of 3 and 5. End of interview. Sorry Chris. It's also worth mentioning that the CASE statement resolves in a single pass where as the IF statements check each condition.
Next came one from Det:
CREATE TABLE Nums ( num int primary key )
GO
DECLARE @i int
SET @i = 1
WHILE @i <= 100
BEGIN
INSERT Nums ( num )
VALUES ( @i )
SET @i = @i + 1
END
SELECT CASE WHEN num % 3 = 0
AND num % 5 = 0 THEN 'BizzBuzz'
WHEN num % 3 = 0 THEN 'Bizz'
WHEN num % 5 = 0 THEN 'Buzz'
ELSE CAST(num AS nvarchar)
END
FROM nums
Det's worked very well, but he created a permanent table and didn't include a drop statement. When I went back to look at the query plan, I got an error. We'll continue the interview, but Det shouldn't count on getting a top spot on the team.
He submitted a second query as well:
DECLARE @i int
SET @i = 1
WHILE @i <=100
BEGIN
SELECT CASE WHEN @i % 3 = 0 AND @i % 5 = 0 THEN 'BizzBuzz'
WHEN @i %3 = 0 THEN 'Bizz'
WHEN @i %5 = 0 THEN 'Buzz'
ELSE CAST(@i AS nvarchar) END
SET @i = @i + 1
END
This worked in a repeatable fashion and instead of printing output lines to the message window, it returned actual results. 100 separate result sets, but even so, it worked. Points for perseverance.
Next came Scott Abrants:
DECLARE @int AS INT;
DECLARE @immdResult AS VARCHAR(100);
SET @int = 1;
WHILE @int < 101
BEGIN
SET @immdResult = CASE WHEN @int % 3 = 0
AND @int % 5 <> 0 THEN 'Bizz'
WHEN @int % 5 = 0
AND @int % 3 <> 0 THEN 'Buzz'
WHEN @int % 3 = 0
AND @int % 5 = 0 THEN 'BizzBuzz'
END ;
PRINT 'The number is ' + CONVERT(VARCHAR(10), @int);
IF LEN(@immdResult) > 0
BEGIN
PRINT @immdResult;
END
SET @int = @int + 1;
END;
Not at all surprisingly, Scott's is dead on accurate and extremely thorough.
One of the discussions that came up was, shouldn't there be a simple way to do this with a CTE? So after lunch, I set out to try it. This is what I came up with:
WITH Nbrs(n) AS (
SELECT 1
UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 100)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
WHEN n%3 = 0 THEN 'Bizz'
WHEN n%5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(8))
END
FROM Nbrs
OPTION (MAXRECURSION 100);
This worked well, looks neat, and it ran in 15ms.
