The T-SQL Quiz

I was reading the latest Simple-Talk email that linked me to an article on Coding Horror, "Why Can't Programmers - Program?" In the article they talked about a simple test that they gave developers to verify their abilities and decide whether or not to continue the interview. Here's the test: Write code that counts from 1 to 100 For each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz'

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.