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
(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
Agile Software Development, Principles, Patterns, and Practices, but you can
see the excerpt in question
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|
|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