Write tsqlt tests for stored procedures.

Question

Write tsqlt tests for stored procedures.

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
April 9, 2014 at 12:17 pm

#287511

Looking for a number of articles here. Basically for each I'd like to see some type of code that you actually use at work. Nothing contrived, though obviously you might change table/column names.
Show the code, explain how you might test this in a few ways (Expected functions/happy path, edge cases, bad input) and then show how a few tests are written.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

evansdennis SSC Enthusiast Points: 151 More actions · Answer 1

Any interest in an article on using CodeSmith to generate tsqlt tests and avoid the need to write boiler plate code.

When testing stored procedures that return from one to N results sets. Or inline table valued functions that return a table. These types of tests will, typically, contain a fair amount of boiler plate. Fake some tables, insert some test data, execute the stored procedure, check the number of rows returned and in some cases the values or the range of those rows.

A couple other examples,

When writing tests for the CRUD style stored procedures, was an insert done, was an update done, does the table contain the updated values.

Tests that check the individual table schemas have not changed. Are the columns originally marked as not null still marked as not null today. Is a column that was created as nchar(60) still nchar(60) or did some one change the size of the column?

Anyway, I use CodeSmith to generate the boiler plate in these types of tests. Yes, I know that goes against the concepts of TDD, it does reduce the grunt work. I would show some snippets of the actual templates used but not go into a lot of detail on the template code.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

TDD is head, or haven't you heard 🙂 - http://david.heinemeierhansson.com/2014/tdd-is-dead-long-live-testing.html

This would be interesting. Maybe you can show Codesmith with a couple examples? If there are more patterns you use, then maybe a second article? I'm trying to teach people how to write tests and jumpstart their knowledge rather than having them stumble a bit.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736212

August 28, 2014 at 11:13 am

#1741699

bump

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

Steve Jones - SSC Editor (8/28/2014)
bump

Would you be interested in a more detailed explaination of why and how the test scripts for the "Tally OH" or the "Running Total" articles where developed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

yes, separate pieces. Some thoughts on why/how you built them and what they accomplish in your mind would be great. Nice to have a setup for testing. I am thinking of a Stairway to testing at some point.

David.Poole SSC Guru Points: 76000 More actions · Answer 6

Steve Jones - SSC Editor (5/5/2014)
TDD is head, or haven't you heard 🙂 - http://david.heinemeierhansson.com/2014/tdd-is-dead-long-live-testing.html

https://watirmelon.com/2012/01/31/introducing-the-software-testing-ice-cream-cone/

While I understand David's frustrations in two years since the article was written I don't see widespread agreement with his views.

Yes, we could and did write software to be proud of before TDD and yes, it is easier than ever to do so now. The important bit is that it makes the developer think about the requirements and how you would demonstrate that stuff works as intended and caters for people trying to use it in ways that weren't intended.

I do agree that there is too much zealotry; blindly championing a path without a clear view of the path ahead or making adjustments for potholes.

Ultimately the goal is to be sure that when a change is made in software you know it will continue to work properly

LinkedIn Profile

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

I think there are plenty of people that use TDD well. I think it makes sense, but it's hard as a discipline. I suspect many people think David was too harsh, but also there isn't a good alternative. Go back to the ice cream cone, or worse, just manual tests. I have quite a few friends that do this. Or they do a few

and eyeball things.

I think that really your testing paradigm should be add a few tests. then a few more. Learn what works, what tests catch bugs, or issues, and then have your ice cream cone shape back down to a pyramid over time.

I definitely agree with zealotry. If you insist on too much testing, say 8 things for every method, then you're going to really make developers upset. I'd rather insist on one test, even post method development, and then let developers learn over time that some of them want 3 tests, some want 8.