tSQLt SQL Server unit testing framework<\/a>:<\/p>\n\r\nCREATE PROCEDURE testHumanResources.[test CalculateAge returns correct age on a birthday]\r\nAS\r\nBEGIN\r\n DECLARE @actualResult INT;\r\n \r\n SELECT @actualResult = HumanResources.CalculateAge('6\/13\/2000', '6\/13\/2010');\r\n \r\n EXEC tSQLt.AssertEquals 10, @actualResult;\r\nEND;\r\nGO\r\n<\/pre>\nSo here are some other test cases we might consider:
1. BirthDate or ReferenceDate are NULL - because we know how special NULLs are. These tests will force you to consider how you want to handle nulls in your application.
2. BirthDate and ReferenceDate are equal - this is a special case. While it is not likely in this particular example because an employee would not be hired the day they were born, an age calculation may happen in other circumstances (e.g. the age of inventory).
3. BirthDate < ReferenceDate - the most common case, but let's take a closer look at the following:
\u00a0\u00a0\u00a0a) when BirthDate is just a few days before ReferenceDate. Is the result 0 as we'd expect?
\u00a0\u00a0\u00a0b) when BirthDate is exactly on a year boundary, as in our test case example above
\u00a0\u00a0\u00a0c) when BirthDate is a day before a year boundary
\u00a0\u00a0\u00a0d) when BirthDate is a day after a year boundary
4. BirthDate > ReferenceDate - this really should never happen, right?
5. When BirthDate is on February 29th of a leap year - leap years are often an overlooked piece of date handling<\/p>\n
Some skepticism<\/strong>
Shouldn't we expect DATEDIFF to just work?<\/em>
Perhaps, but we also need to validate our usage of it. If you write these test cases against the CalculateAge function, you may be surprised to find that there is a bug. (That's for homework, by the way)<\/p>\nWhy do we test things that should never happen?<\/em>
In the test cases #1, #2 and #4 above, we wouldn't really expect these values to happen in real life. #1 for example, may be entirely preventable if BirthDate is a NOT NULL column for the Employee table. However, if the CalculateAge function is applied to another purpose, it is reasonable that NULL values could be present.<\/p>\n#2 and #4 came to mind when working on a different application involving newborns. Obviously in this case, the BirthDate could be today; and if some crafty user decided that BirthDate was a good spot for expected due date (or made a typo and our application wasn't smart enough to catch it) a future date could be present. Besides, if the reference date could be in the past, it makes it possible for the BirthDate to be in the future relative to the reference date.<\/p>\n
What are all these strange cases for #3 about?<\/em>
Since the function calculates the age, one of the boundaries is the birthday anniversary. On either side of the employee's birthday, the value of age should be different. It's important to test boundary conditions because these are common places for defects to exist.<\/p>\nHomework<\/strong>
We're not done with this example yet, but I think you have enough to chew on for this time \ud83d\ude42
Homework for this time is to write some of the test cases and fix the bug in CalculateAge.<\/p>\nWhat's next?<\/strong>
Next time we'll take a break from looking at code examples and talk about helpful tools and techniques for identifying test cases. Then we'll revisit this example and discuss a bit more about that pesky GETDATE() function and how to test the MinorEmployee view independently of the CalculateAge() function.<\/p>\n","protected":false},"excerpt":{"rendered":"Last time, in Identifying Test Cases #1, we looked at some simple examples of identifying test cases for SQL statements. This time, we’re going to look at a more complex example in the form of a database view. Our ExampleSuppose [more…]<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-general"],"acf":[],"yoast_head":"\nIdentifying Test Cases #2<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identifying Test Cases #2\" \/>\n<meta property=\"og:description\" content=\"Last time, in Identifying Test Cases #1, we looked at some simple examples of identifying test cases for SQL statements. This time, we're going to look at a more complex example in the form of a database view. Our ExampleSuppose [more...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2010-06-15T17:26:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:04:17+00:00\" \/>\n<meta name=\"author\" content=\"dennis\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dennis\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/\"},\"author\":{\"name\":\"dennis\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\"},\"headline\":\"Identifying Test Cases #2\",\"datePublished\":\"2010-06-15T17:26:54+00:00\",\"dateModified\":\"2014-11-13T19:04:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/\"},\"wordCount\":814,\"commentCount\":0,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/\",\"name\":\"Identifying Test Cases #2\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2010-06-15T17:26:54+00:00\",\"dateModified\":\"2014-11-13T19:04:17+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/84\\\/identifying-test-cases-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identifying Test Cases #2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/da93e377379dd64d1428fd3d130269fd\",\"name\":\"dennis\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g\",\"caption\":\"dennis\"},\"sameAs\":[\"http:\\\/\\\/www.curiouslycorrect.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identifying Test Cases #2","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/","og_locale":"en_US","og_type":"article","og_title":"Identifying Test Cases #2","og_description":"Last time, in Identifying Test Cases #1, we looked at some simple examples of identifying test cases for SQL statements. This time, we're going to look at a more complex example in the form of a database view. Our ExampleSuppose [more...]","og_url":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2010-06-15T17:26:54+00:00","article_modified_time":"2014-11-13T19:04:17+00:00","author":"dennis","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"dennis","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/"},"author":{"name":"dennis","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd"},"headline":"Identifying Test Cases #2","datePublished":"2010-06-15T17:26:54+00:00","dateModified":"2014-11-13T19:04:17+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/"},"wordCount":814,"commentCount":0,"articleSection":["General"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/","url":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/","name":"Identifying Test Cases #2","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2010-06-15T17:26:54+00:00","dateModified":"2014-11-13T19:04:17+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd"},"breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/84\/identifying-test-cases-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Identifying Test Cases #2"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/da93e377379dd64d1428fd3d130269fd","name":"dennis","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/166ceda64e242f6ea01d467d0b6121b7264f9127efd3531ece1126385c50a385?s=96&d=mm&r=g","caption":"dennis"},"sameAs":["http:\/\/www.curiouslycorrect.com"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-1m","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}