# Bowling Challenge - The Results

,

Many moons I ago I wrote The Bowling Challenge, not realizing how much interest the challenge would generate, or the lack of time I was about to have to try to test them all. I won't say I'm caught up entirely, but I did start working through the responses again this weekend. I'm going to show you one response that I believe scores a game correctly, but since I took so long I'll picking another winning entry soon - I'll be looking for one that takes a different approach than this one.

To start with, if you're not a ace bowling scorer, you might some this online scoring calculator handy.

I identified the following test scenarios that I thought would do a good job of testing the implementation. If you can think of a scenario that could reveal a bug that these don't, drop me a note and I'll add it to the test suite.

• Perfect game. Bowler threw strikes in all 10 frames and on the 'extra'

ball at the end of the 10th frame. 10th frame is the worst exception case,

want to be sure we test it.

• No strikes, no spares, only score pins on first ball of each frame.

• No strikes, no spares, score on both frames.

• All spare game, no score on extra ball in 10th. Testing the margins again.

• All spare game, strike on extra ball in 10th.

• 9 spares, all strikes in last frame. Not sure this is a good test, but

doesn't hurt to test it

• Absolutely no score

I didn't test validation. We're trying to prove the algorithm, I'm assuming

that anyone who can get the algorithm to work can write the validation to check

for too many frames, more than 10 pins in a frame, etc.

in a test database:

 CREATE TABLE [Scores] ([RowID] [int] IDENTITY (1, 1) NOT NULL ,[UserID] [int] NOT NULL ,[DateAdded] [smalldatetime] NOT NULL CONSTRAINT [DF__Scores__DateAdde__76CBA758] DEFAULT (getdate()),[FrameNumber] [tinyint] NOT NULL ,[Ball1] [tinyint] NOT NULL ,[Ball2] [tinyint] NOT NULL ,[NumberOfPins] [smallint] NULL ,CONSTRAINT [PK_Scores] PRIMARY KEY CLUSTERED ([RowID]) ON [PRIMARY] ) ON [PRIMARY]GO

Even though it's a little long, I'm going to put the code inline here - both

to make it easier to read and because I know it'll get included in the next of

our 'Best of SQLServerCentral' volumes!

```create Procedure dbo.usp_ScoreBowlingGame
@UserID int,
@Ball1 tinyint,
@Ball2 tinyint = 0,
@Ball3 tinyint = 0
AS
--Score a bowling game
Set @Ball1 = isnull(@Ball1, 0)
Set @Ball2 = isnull(@Ball2, 0)
Set @Ball3 = isnull(@Ball3, 0)
Set NoCount On
Declare @Score smallint
Declare @FrameBall1 smallint
Declare @FrameBall2 smallint
Declare @NextBall1 smallint
Declare @NextBall2 smallint
Declare @NumberOfPins smallint
Declare @RunningTotal smallint
Set @RunningTotal = 0
--calculate next frame
From Scores with (nolock)
Where userid = @UserID
--dont allow extra frames or pin count higher than 10
if (@NextFrameNumber < 10 and @Ball1 + @Ball2 <= 10)
or (@NextFrameNumber = 10 and @Ball1 + @Ball2 + @Ball3 <= 30)
begin
--insert frame pins
insert into scores(userid, framenumber, ball1, ball2, numberofpins)
values (@UserID, @NextFrameNumber, @Ball1, @Ball2, null)
--select all frames in order
declare bowling_cursor cursor for
from scores with (nolock)
where userid = @UserID
open bowling_cursor
fetch next from bowling_cursor into @FrameNumber, @FrameBall1, @FrameBall2, @NumberOfPins
while @@Fetch_Status = 0
begin
if @NumberOfPins is NULL
begin
--reset value
Set @Score = NULL
begin
--is strike
if @FrameBall1 = 10
begin
begin
if exists(select * from scores where framenumber = @FrameNumber + 1 and userid = @UserID)
begin
Select @NextBall1 = ball1, @NextBall2 = ball2 from scores
and userid = @UserID
--if strike
if @NextBall1 = 10
begin
if exists(select * from scores
and userid = @UserID)
begin
Select @NextBall2 = ball1 from scores
and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
else
begin
Set @Score = Null
end
end
else
begin
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
end
end
begin
if exists(select * from scores
begin
Select @NextBall1 = ball1, @NextBall2 = ball2 from scores
and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1 + @NextBall2
Set @Score = @RunningTotal
end
end
end
--is spare
if @FrameBall1 + @FrameBall2 = 10  and @FrameBall1 < 10
begin
if exists(select * from scores where framenumber = @FrameNumber + 1 and userid = @UserID)
begin
Select @NextBall1 = ball1 from scores where framenumber = @FrameNumber + 1 and userid = @UserID
Set @RunningTotal = @RunningTotal + 10 + @NextBall1
Set @Score = @RunningTotal
end
end
--is other
if @FrameBall1 + @FrameBall2 < 10
begin
Set @RunningTotal = @RunningTotal + @FrameBall1 + @FrameBall2
Set @Score = @RunningTotal
end
end
--if frame 10
begin
Set @RunningTotal = @RunningTotal + @Ball1 + @Ball2 + @Ball3
Set @Score = @RunningTotal
print 'Game Over'
end
if @Score is not null
begin
--update score for current frame and user
update scores set numberofpins = @Score
end
end
else
begin
--print 'Skipped frame: ' + cast(@FrameNumber as varchar(2)) + ', already scored.'
Select @RunningTotal = numberofpins from scores
end
fetch next from bowling_cursor into @FrameNumber, @FrameBall1, @FrameBall2, @NumberOfPins
end
close bowling_cursor
deallocate bowling_cursor
end```
I think my first thought on examining this solution is that isn't set

based. Not a requirement, only requirement is that the answer has to be correct!

I noted some decent validation up front to deal with nulls, pins that exceed the

allowable range. Cursor being used - note that it's not specified as local or

readonly, something easily corrected and doesn't affect the test results. I also

see that the code is readable, good variable names, and some comments, enough

for me to decipher the flow. The assumption is that the proc is called once for

each frame, passing in the number of pins for the first and second balls (and

for the third ball on the tenth frame).

I set up a new test database, created the table as noted above, created the

procedure. I then ran through the following tests.

```declare @Score int
set nocount on
--test perfect game
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 10, 10
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print 'Perfect game'
if @Score = 300
print '--Passed'
else
print '--Failed'
--test all no strike, no spare game
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 0, 0
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print 'No strike/no spare'
if @Score = 10
print '--Passed'
else
print '--Failed'
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 1, 0
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print 'No strike/no spare, scored in all frames'
if @Score = 20
print '--Passed'
else
print '--Failed'
--test all all spare game
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print 'All spare game'
if @Score = 109
print '--Passed'
else
print '--Failed'
--test all all spare game, strike on last ball
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 10
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print 'All spare game, strike on last ball'
if @Score = 119
print '--Passed'
else
print '--Failed'
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 1, 9, 0
exec dbo.usp_ScoreBowlingGame 1, 10, 10, 10
select @Score = max(numberofpins) from scores with (nolock) where userid = 1
print '9 spares, all strikes in last frame'
if @Score = 138
print '--Passed'
else
print '--Failed'
delete from scores
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
exec dbo.usp_ScoreBowlingGame 1, 0, 0, 0
select @Score = isnull(max(numberofpins), 0) from scores with (nolock) where userid = 1
print 'Total loss!'
if @Score = 0
print '--Passed'
else
print '--Failed'
```

All tests passed. The only real negative I can see is that the value for the extra ball in the tenth frame is not persisted. The final score returned is correct, but without the stored value you wouldn't be able to rescore the game if needed. This is just housekeeping, easy enough to add a third column to the table even though it would only get used on the tenth frame.

I'm please to announce that the team of  Jon Winer, Matt Owen, and Namho Jung will each receive a SSC Polo Shirt for their solution. As Im mentioned earlier, I'll be posting another solution as soon as I find one that is interesting AND passes all the tests. To all of you who took time to participate, I hope you found it as much fun as my team at work did - and don't give up hope, you might yet win!