I wrote an article recently (Is

all code really code? One question from an interview) that discusses why I

think developers look at TSQL as being "different" than "real" code. The article

definitely provoked a good discussion, you may find it interesting just for that

part alone. So what about you, SQL reader? While I don't argue that you should use the

correct tool for the task, are you capable of writing something more than the

average insert, update, and delete stored procedures? Is that a challenge?!

Now bear with me for a moment while we cover one other background topic.

Recently I conducted a training session with my team to introduce them to

Test Driven Development

(TDD). If you're not familiar with it, the idea is simple - before you write any

code, you write a test that fails, then you make the test pass, and so on. It's

not something that seems obviously more productive when you talk about it and in

truth I've yet to do it enough to be able to give a fair opinion. If you're at

the point in your career where the following makes sense, you might be ready to

tackle it - using TDD results in code that is more easily tested. A nice side

affect is you get almost 100% code coverage.

For the training session the task was to use TDD to create a set of objects

that would score a bowling game. I drew the inspiration from Robert Martin's

fine book

Agile Software Development, Principles, Patterns, and Practices, but you can

see the excerpt in question

here.

Once we got into it we found that designing tests and objects wasn't the hard

part, it was the scoring algorithm. Almost everyone (we pair programmed) had a

game object and a collection of frame objects with just minor variations.

If you've bowled before you know that the scoring is a little, well,

complicated. If you haven't you should go once! Until you have time, you can

review how to score here.

No one got their code to work in the time allotted, but we had some fun and got

to try something new.

Ready for the challenge?

Create a table using the following script:

create table Scores (
RowID int identity(1,1) not null,
UserID int not null,
DateAdded smalldatetime default GetDate(),
FrameNumber tinyint not null,
NumberOfPins tinyint not null)

For the challenge we won't worry about indexes or security. It'll be up to

you to populate the table with data to use for Part 2 as needed.

Part 2 is to write a stored procedure using pure TSQL only (no sp_oa~ procs

or custom extended stored procedures) that will calculate the score for each

frame that is possible to score. For example, if the bowler has completed four

throws hitting 4 pins each time, the results would look something like this:

Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)
Insert into Scores (UserID, FrameNumber, NumberOfPins) Values (1, 1, 4)

Running our stored proc should return something like the output below. They

earned 8 pins in the first frame, 8 pins in the second frame for a cumulative

total in the second frame of 16 pins.

Frame 1    8
Frame 2    16

If they have completed all ten frames the tenth frame should contain the

total score for the entire game. Here is one hint: a perfect game should have a

final score of 300.

Send me your code as a script file - don't post in the discussion area! If

you have questions to clarify the requirements or just on TDD, please do post

those. The contest will be open for 30 days from the date this is published in

the newsletter. Once the contest ends, we'll start testing. We'll pick a winner

from among the entries that passes all the tests. Steve Jones will be the final

judge. And what do you get for winning? It's more work than you think, so we've

got a prize worthy of it

	A free printed copy of our Best of SQLServerCentral.com Volume 2
and
A SQLServerCentral.com shirt!

Want to have a little more fun? Challenge the other DBA's on your team, or

see if the developers can do it, even if they have to use code instead of

TSQL.