SQLServerCentral Article

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.

To test the entry this article is based on, start by executing the following

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 @NextFrameNumber tinyint

Declare @Score smallint

Declare @FrameNumber tinyint

Declare @FrameBall1 smallint

Declare @FrameBall2 smallint

Declare @NextBall1 smallint

Declare @NextBall2 smallint

Declare @NumberOfPins smallint

Declare @RunningTotal smallint

Set @RunningTotal = 0

--calculate next frame

Select @NextFrameNumber = isnull(Max(FrameNumber), 0) + 1

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

select framenumber, ball1, ball2, numberofpins

from scores with (nolock)

where userid = @UserID

and framenumber <= @NextFrameNumber

order by framenumber

open bowling_cursor

fetch next from bowling_cursor into @FrameNumber, @FrameBall1, @FrameBall2, @NumberOfPins

while @@Fetch_Status = 0

begin

--if frame already scored, skip

if @NumberOfPins is NULL

begin

--reset value

Set @Score = NULL

if @FrameNumber < 10

begin

--is strike

if @FrameBall1 = 10

begin

if @FrameNumber <> 9

begin

if exists(select * from scores where framenumber = @FrameNumber + 1 and userid = @UserID)

begin

Select @NextBall1 = ball1, @NextBall2 = ball2 from scores

where framenumber = @FrameNumber + 1

and userid = @UserID

--if strike

if @NextBall1 = 10

begin

if exists(select * from scores

where framenumber = @FrameNumber + 2

and userid = @UserID)

begin

Select @NextBall2 = ball1 from scores

where framenumber = @FrameNumber + 2

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

if @FrameNumber = 9

begin

if exists(select * from scores

where framenumber = @FrameNumber + 1 and userid = @UserID)

begin

Select @NextBall1 = ball1, @NextBall2 = ball2 from scores

where framenumber = @FrameNumber + 1

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

if @FrameNumber = 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

where framenumber = @FrameNumber and userid = @UserID

end

end

else

begin

--print 'Skipped frame: ' + cast(@FrameNumber as varchar(2)) + ', already scored.'

Select @RunningTotal = numberofpins from scores

where framenumber = @FrameNumber and userid = @UserID

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!

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating