How to name your unit tests: Four test naming conventions

From our previous post, we learned about four common mistakes we make when writing our first unit tests. One of them is not to follow a naming convention. Let’s see four naming conventions for our unit tests.

Test names should tell the scenario under test and the expected result. Writing long names is acceptable since test names should show the purpose behind what they’re testing. When writing tests, prefer good test names over assertion messages.

These are four common naming conventions we can use. Let’s continue to use Stringie, a (fictional) library to manipulate strings. Stringie has a Remove() method to remove a substring from the beginning or end of an input string.

1. UnitOfWork_Scenario_ExpectedResult

[TestClass]
public class RemoveTests
{
    [TestMethod]
    public void Remove_NoParameters_ReturnsEmpty() {}

    [TestMethod]
    public void Remove_ASubstring_RemovesOnlyASubstring() {}
}

We find this naming convention in the book The Art of Unit Testing. This convention uses underscores to separate the unit of work or entry point, the test scenario, and the expected behavior.

With this convention, we can read our test names out loud like this: “When calling Remove with no parameters, then it returns empty.”

Grab a free copy of my ebook Unit Testing 101: From Zero to Your First Tests on my Gumroad page. I include these four naming conventions and three more chapters to help you start writing your first unit tests in C#.

2. Plain English sentence

[TestClass]
public class RemoveTests
{
    [TestMethod]
    public void Returns_empty_with_no_parameters() {}

    [TestMethod]
    public void Removes_only_a_substring() {}
}

Unlike the “UnitOfWork_Scenario_ExpectedResult” convention, this convention strives for a less rigid name structure.

This convention uses sentences in plain English for test names. We describe in a sentence what we’re testing in a language easy to understand, even for non-programmers. For more readability, we separate each word in our sentence with underscores.

This convention considers smells adding method names and filler words like “should” or “should be” in our test names. For example, instead of writing, “should_remove_only_a_substring”, we should write “removes_only_a_substring”.

You could read more about this convention in Vladimir Khorikov’s post: You are naming your tests wrong!.

3. A sentence from classes and methods names

[TestClass]
public class RemoveGivenASubstring
{
    [TestMethod]
    public void RemovesThatSubstring() {}

    [TestMethod]
    public void RemovesThatSubstringFromTheEnd() {}
}

This naming convention uses sentences in plain English too. In this case, class names will act as the subject of our sentences and method names as the verb and the complement. We write units of work or entry points in class names and expected results in method names.

Also, we can split different scenarios into separate classes. In the class names, we add the keyword Given followed by the scenario under test.

For our Remove() method, we can name our test class RemoveGivenASubstring and our test methods RemovesOnlyASubstring and RemovesSubstringFromTheEnd.

With this convention, we can read our test names like complete sentences in the “Test Explorer” menu in Visual Studio when we group our tests by class. Like this: “Remove, given a substring, removes that substring.”

You can read more about this convention in ardalis’ post: Unit Test Naming Convention.

4. Nested classes and methods

[TestClass]
public class RemoveTests
{
    [TestMethod]
    public void ReturnsEmpty() {}
    
    [TestClass]
    public class GivenASubstring
    {
        [TestMethod]
        public void RemovesThatSubstring() {}

        [TestMethod]
        public void RemovesThatSubstringFromTheEnd() {}
    }
}

This last convention uses sentences split into class and method names too. Unlike the previous naming convention, each scenario has its own nested class.

For example, instead of having a test class RemoveGivenASubstring, we create a nested class GivenASubstring inside a RemoveTests class.

You can learn more about this last convention in Kevlin Henney’s presentation Structure and Interpretation of Test Cases on YouTube.

Voilà! That’s how we can name our unit tests. Remember naming things is hard. Pick one of these four naming conventions and stick to it. But, if you inherit a codebase, prefer the convention already in use. I hope you can write more readable test names after reading this post.

If you want to practice naming unit tests, check my Unit Testing 101 repository. There you will find the test names that Stringie developers wrote. Your mission, Jim, should you choose to accept it, is to write better names.

If you’re new to unit testing, read my post on how to write your first unit tests in C# and check the 4 common mistakes when writing your first tests. Don’t miss the rest of my Unit Testing 101 series where I also cover mocking, assertions, and other best practices.

Happy testing!

Four common mistakes when writing your first unit tests

Last time, we covered how to write our first unit tests with C# and MSTest. We started from a Console program and converted it into our first unit tests. We wrote those tests for Stringie, a (fictional) library to manipulate strings with more readable methods. This time, we will cover how NOT to write unit tests. These are four common mistakes we should avoid when writing our first unit tests.

TL;DR

1. Do not follow a naming convention
2. Do not use the right assertion methods
3. Do not have a single assertion per test
4. Repeat logic in your assertions

Mistake 1: Do not follow a naming convention

First, keep your tests in the right place. Have one test project per project, one test class per class. Add the suffix “Tests” in the name of your test projects and classes.

Choose a naming convention for your test names and stick to it.

In our previous post, we covered two naming conventions. An “ItShould” sentence and the “UnitOfWork_Scenario_ExpectedResult”, a three-part name separated with underscores. You can choose the one you like the most.

That time, for Stringie Remove() method, following the “UnitOfWork_Scenario_ExpectedResult” convention, we wrote test names like these ones:

[TestClass]
public class RemoveTests
{
    [TestMethod]
    public void Remove_ASubstring_RemovesThatSubstring() { }
	
    [TestMethod]
    public void Remove_NoParameters_ReturnsEmpty() { }
}

Every test should tell the scenario under test and the expected result. We shouldn’t worry about long test names. But, let’s stop naming our tests: Test1, Test2, and so on.

Don’t prefix our test names with “Test.” If we’re using a testing framework that doesn’t need keywords in our test names, let’s stop doing that. With MSTest, we have attributes like [TestClass] and [TestMethod] to mark our methods as tests.

Also, don’t use filler words like “Success” or “IsCorrect” in our test names. Instead, let’s tell what “success” and “correct” means for that test. Is it a successful test because it doesn’t throw exceptions? Is it successful because it returns a value? Make your test names easy to understand.

Grab a free copy of my ebook Unit Testing 101: From Zero to Your First Tests on my Gumroad page. I include these four mistakes and three more chapters to help you start writing your first unit tests in C#.

Mistake 2: Do not use the right assertion methods

Follow the Arrange/Act/Assert principle. Separate the body of your tests to differentiate these three parts.

For the Assert part of your tests, make sure to use an assertion library. MSTest, NUnit, and XUnit are the three most popular ones for C#.

Use the right assertion methods of your library. For example, MSTest has assertion methods for strings, collections, and other objects. For a list of the most common MSTest assertions methods, check the MSTest Cheatsheet in Unit Testing 101.

Please, don’t do.

Assert.AreEqual(null, result);
// or
Assert.AreEqual(true, anotherResult);

Prefer.

Assert.IsNull(result);
// or
Assert.IsTrue(anotherResult);

Mistake 3: Do not have a single assertion per test

Have only one Act and Assert part in your tests. Don’t repeat the same Act part with different test values in a single test.

Please, avoid writing tests like this one.

[TestMethod]
public void Remove_SubstringWithDifferentCase_RemovesSubstring()
{
    var str = "Hello, world!";

    var transformed = str.RemoveAll("Hello").IgnoringCase();
    Assert.AreEqual(", world!", transformed);

    transformed = str.RemoveAll("HELLO").IgnoringCase();
    Assert.AreEqual(", world!", transformed);

    transformed = str.RemoveAll("HeLlO").IgnoringCase();
    Assert.AreEqual(", world!", transformed);
}

Here, we tested the same method with different test values in a single test.

Also, avoid writing tests like this one.

[TestMethod]
public void Remove_SubstringWithDifferentCase_RemovesSubstring()
{
    var str = "Hello, world!";

    var testCases = new string[]
    {
        "Hello",
        "HELLO",
        "HeLlO"
    };
    string transformed;
    foreach (var str in testCases)
    {
        transformed = str.RemoveAll("Hello").IgnoringCase();
        Assert.AreEqual(", world!", transformed);
    }
}

This time, to avoid repetition, we put the test values in an array and looped through them to test each value.

If we want to test the same scenario with different test values, let’s use parameterized tests instead.

How to write Parameterized tests with MSTest

To write a parameterized test with MSTest, we can follow these steps:

1. Replace the [TestMethod] attribute with the [DataTestMethod] attribute in your test.
2. Add [DataRow] attributes for each set of test values.
3. Add parameters for each test value inside the [DataRow] attributes.
4. Use the input parameters in your test to arrange, act or assert.

Let’s convert the previous test with repeated test values into a parameterized test.

[DataTestMethod]
[DataRow("Hello")]
[DataRow("HELLO")]
[DataRow("HeLlo")]
public void Remove_SubstringWithDifferentCase_RemovesSubstring(string substringToRemove)
{
    var str = "Hello, world!";

    var transformed = str.RemoveAll(substringToRemove).IgnoringCase();

    Assert.AreEqual(", world!", transformed);
}

With parameterized tests, we have separate tests. Inside Visual Studio, in the “Test Explorer” menu, we will have one result per each [DataRow] attribute in the parent test.

It’s easier to troubleshoot parameterized tests when our tests fail for a single test value.

Mistake 4: Repeat logic in your assertions

I can’t stress this enough.

Don’t repeat the logic under test in your assertions. Use known, hard-coded, pre-calculated values instead.

We shouldn’t copy the tested logic and paste it into a private method in our tests to use it in our assertions. We will have code and bugs in two places.

Please, don’t write assertions like the one in this test.

[TestMethod]
public void Remove_ASubstring_RemovesThatSubstringFromTheEnd()
{
    string str = "Hello, world!";

    string transformed = str.Remove("world!").From(The.End);

    var position = str.IndexOf("world!");
    var expected = str.Substring(0, position);
    Assert.AreEqual(expected, transformed);
}

For this test, instead of using the Substring() method to remove the input string, use a known expected value. Write Assert.AreEqual("Hello,", transformed). For example,

[TestMethod]
public void Remove_ASubstring_RemovesThatSubstringFromTheEnd()
{
    string str = "Hello, world!";

    string transformed = str.Remove("world!").From(The.End);

    // Let's use a known value in our assertions
    Assert.AreEqual("Hello,", transformed)
}

Voilà! These are four common mistakes when writing our first unit tests. Remember to put your test in the right places following a naming convention. Also, keep one assertion per test, and don’t repeat logic in your assertions. You will have better tests by avoiding these mistakes.

If you want to practice identifying and fixing these mistakes, check my Unit Testing 101 repository. You will find the tests that Stringie developers wrote and some other misatkes mistakes they made. Your mission, Jim, should you choose to accept it, is to fix them.

If you’re new to unit testing, read my post Unit Testing 101 to write your first unit tests in C#. Take a closer look at that last mistake on my post Don’t duplicate logic in your Asserts. For more advanced tips on writing unit tests, check my post how to write good unit tests.

And don’t miss the rest of my Unit Testing 101 series for more subjects on unit testing.

Happy testing!

Unit Testing 101: Write your first unit test in C# with MSTest

Do you want to start writing unit tests and you don’t know how to start? Were you asked to write some unit tests on a past interview? Let’s see what a unit test is and how to write your first unit tests in C#.

1. What is a Unit test?

The book The Art of Unit Testing defines a unit test as “an automated piece of code that invokes a unit of work in the system and then checks a single assumption about the behavior of that unit of work.”

From the previous definition, a unit of work is any logic exposed through public methods. Often, a unit of work returns a value, changes the internals of the system, or makes an external invocation.

If that definition answers how to test public methods, we might ask: “What about private methods?” Short answer: we don’t test them. We test private methods when we call our code through its public methods.

In short, a unit test is code that invokes some code under test and verifies a given behavior of that code.

2. Why should we write unit tests?

Have you ever needed to change your code, but you were concerned about breaking something? I’ve been there too.

The main reason to write unit tests is to gain confidence. Unit tests allow us to make changes, with confidence that they will work. Unit tests allow change.

Unit tests work like a “safety net” to prevent us from breaking things when we add features or change our codebase.

In addition, unit tests work like living documentation. The first end-user of our code is our unit tests. If we want to know what a library does, we should check its unit tests. Often, we will find non-documented features in the tests.

If you want this guide in pdf format, grab a free copy of my ebook Unit Testing 101: From Zero to Your First Tests on my Gumroad page. I include three more chapters to help you start writing your first unit tests in C#.

3. What makes a good unit test?

Now, we know what a unit test is and why we should write them. The next question we need to answer is: “What makes a test a good unit test?” Let’s see what all good unit tests have in common.

Our tests should run quickly

The longer our tests take to run, the less frequently we run them. And, if we don’t run our tests often, we have doors opened to bugs.

Our tests should run in any order

Tests shouldn’t depend on the output of previous tests to run. A test should create its own state and not rely upon the state of other tests.

Our tests should be deterministic

No matter how many times we run our tests, they should either fail or pass every time. We don’t want our test to use random input, for example.

Our tests should validate themselves

We shouldn’t debug our tests to make sure they passed or failed. Each test should determine the success or failure of the tested behavior. Let’s imagine we have hundreds of tests, and to make sure they pass, we have to debug every one of them. What’s the point, then?

“It could be considered unprofessional to write code without tests” - Robert Martin, The Clean Coder

4. Let’s write our first unit test with MSTest

Let’s write some unit tests for Stringie, a (fictional) library to manipulate strings with more readable methods.

One of Stringie methods is Remove(). It removes chunks of text from a string. For example, Remove() receives a substring to remove. Otherwise, it returns an empty string if we don’t pass any parameters.

"Hello, world!".Remove("Hello");
// ", world!"

"Hello, world!".Remove();
// ""

Here’s the implementation of the Remove() method for the scenario without parameters.

namespace Stringie
{
    public static class RemoveExtensions
    {
        public static RemoveString Remove(this string source)
        {
            return new RemoveString(source);
        }
    }

    public class RemoveString
    {
        private readonly string _source;

        internal RemoveString(string source)
        {
            _source = source;
        }

        public static implicit operator string(RemoveString removeString)
        {
            return removeString.ToString();
        }

        public override string ToString()
        {
            return _source != null ? string.Empty : null;
        }
    }
}

Let’s write some tests for the Remove() method. We can write a Console program to test these two scenarios.

using Stringie;
using System;

namespace TestProject
{
    class Program
    {
        static void Main(string[] args)
        {
            var helloRemoved = "Hello, world!".Remove("Hello");
            if (helloRemoved == ", world!")
            {
                Console.WriteLine("Remove Hello OK");
            }
            else
            {
                Console.WriteLine($"Remove Hello failed. Expected: ', world!'. But it was: '{helloRemoved}'");
            }

            var empty = "Hello, world!".Remove();
            if (string.IsNullOrEmpty(empty))
            {
                Console.WriteLine("Remove: OK");
            }
            else
            {
                Console.WriteLine($"Remove failed. Expected: ''. But it was: {empty}");
            }

            Console.ReadKey();
        }
    }
}

However, these aren’t real unit tests. They run quickly, but they don’t run in any order and they don’t validate themselves.

Where should we put our tests?

Let’s create a new project. Let’s add to the solution containing Stringie a new project of type “MSTest Test Project (.NET Core)”. Since we’re adding tests for the Stringie project, let’s name our new test project Stringie.UnitTests.

It’s my recommendation to put our unit tests in a test project named after the project they test. We can add the suffix “Tests” or “UnitTests”. For example, if we have a library called MyLibrary, we should name our test project: MyLibrary.UnitTests.

In our new test project, let’s add a reference to the Stringie project.

After adding the new test project, Visual Studio created a file UnitTest1.cs. Let’s rename it! We are adding tests for the Remove() method, let’s name this file: RemoveTests.cs.

One way of making our tests easy to find and group is to separate them in files named after the unit of work or entry point of the code we’re testing. Let’s add the suffix “Tests”. For a class MyClass, let’s name our file: MyClassTests.

MSTest

Now, let’s see what’s inside our RemoveTests.cs file.

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Stringie.UnitTests
{
    [TestClass]
    public class RemoveTests
    {
        [TestMethod]
        public void TestMethod1()
        {
        }
    }
}

It contains one normal class and method. However, they’re annotated with two unusual attributes: [TestClass] and [TestMethod]. These attributes tell Visual Studio that our file contains unit tests to run.

The TestClass and TestMethod attributes belong to a project called MSTest. Microsoft Test Framework (MSTest) is an open-source unit testing framework. MSTest comes installed with Visual Studio.

Unit testing frameworks help us to write and run unit tests. Also, they create reports with the results of our tests. Other common unit testing frameworks include NUnit and XUnit.

How should we name our tests?

Let’s replace the name TestMethod1 with a name that follows a naming convention.

We should use naming conventions to show the feature tested and the purpose behind of our tests. Test names should tell what they’re testing.

A name like TestMethod1 doesn’t say anything about the code under test and the expected result.

ItShould

One naming convention for our test names uses a sentence to tell what they’re testing. Often, these names start with the prefix “ItShould” followed by an action. For our Remove() method, it could be:

• ItShouldRemoveASubstring
• ItShouldReturnEmpty

UnitOfWork_Scenario_ExpectedResult

Another convention uses underscores to separate the unit of work, the test scenario, and the expected behavior in our test names. If we follow this convention for our example tests, we name our tests:

• Remove_ASubstring_RemovesThatSubstring
• Remove_NoParameters_ReturnsEmpty

With this convention, we can read our test names out loud like this: “When calling Remove with a substring, then it removes that substring.”

Following the second naming convention, our tests look like this:

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Stringie.UnitTests
{
    [TestClass]
    public class RemoveTests
    {
        [TestMethod]
        public void Remove_ASubstring_RemovesThatSubstring()
        {
        }

        [TestMethod]
        public void Remove_NoParameters_ReturnsEmpty()
        {
        }
    }
}

These names could look funny at first glance. We should use compact names in our code. However, when writing unit tests, readability is important. Every test should state the scenario under test and the expected result. We shouldn’t worry about long test names.

How should we write our tests? The AAA Principle

Now, let’s write the body of our tests.

To write our tests, let’s follow the Arrange/Act/Assert (AAA) principle. Each test should contain these three parts.

In the Arrange part, we create input values to call the entry point of the code under test.

In the Act part, we call the entry point to trigger the logic being tested.

In the Assert part, we verify the expected behavior of the code under test.

Let’s use the AAA principle to replace one of our examples with a real test. Also, let’s use line breaks to visually separate the AAA parts.

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Stringie.UnitTests
{
    [TestClass]
    public class RemoveTests
    {
        [TestMethod]
        public void Remove_NoParameters_ReturnsEmpty()
        {
            string str = "Hello, world!";

            string transformed = str.Remove();

            Assert.AreEqual(0, transformed.Length);
        }
    }
}

We used the Assert class from MSTest to write the Assert part of our test. This class contains methods like AreEqual(), IsTrue() and IsNull().

The AreEqual() method checks if the result from a test is equal to an expected value. In our test, we used it to verify the length of the transformed string. We expect it to be zero.

Don’t repeat logic in the assertions

Let’s use a known value in the Assert part instead of repeating the logic under test in the assertions. It’s OK to hardcode some expected values in our tests. We shouldn’t repeat the logic under test in our assertions. For example, we can use well-named constants for our expected values.

Here’s an example of how not to write the Assertion part of our second test.

[TestMethod]
public void Remove_ASubstring_RemovesThatSubstring()
{
    string str = "Hello, world!";

    string transformed = str.Remove("Hello");

    var position = str.IndexOf("Hello");
    var expected = str.Substring(position + 5);
    Assert.AreEqual(expected, transformed);
}

Notice how it uses the Substring() method in the Assert part to find the string without the Hello substring. A better alternative is to use the expected result in the AreEqual() method.

Let’s rewrite our last test to use an expected value instead of repeating the logic being tested.

[TestMethod]
public void Remove_ASubstring_RemovesThatSubstring()
{
    string str = "Hello, world!";

    string transformed = str.Remove("Hello");

    // Here we use the expected result ", world!"
    Assert.AreEqual(", world!", transformed)
}

Repeating the logic under test is only one of the most common mistakes when writing unit tests.

5. How can we run a test inside Visual Studio?

To run a test, let’s right-click on the [TestMethod] attribute of the test and use “Run Test(s)”. Visual Studio will compile your solution and run the test you clicked on.

After the test runs, let’s go to the “Test Explorer” menu. There we will find the list of tests. A passed test has a green icon. If we don’t have the “Test Explorer”, we can use the “View” menu in Visual Studio and click “Test Explorer” to display it.

That’s a passing test! Hurray!

If the result of a test isn’t what was expected, the Assertion methods will throw an AssertFailedException. This exception or any other unexpected exception flags a test as failed.

6. MSTest Cheatsheet

These are some of the most common Assertion methods in MSTest.

7. Conclusion

Voilà! That’s how you write your first unit tests in C# with MSTest. Don’t forget to follow naming conventions and use the Assert class when writing unit tests.

If you want to practice writing more tests for Stringie, check my Unit Testing 101 repository on GitHub.

In this repo, you will find two lessons: one to write some unit tests and another to fix some unit tests.

For more content about unit testing, don’t miss the entire series Unit Testing 101.

This post was originally published on exceptionnotfound.net as part of the Guest Writer Program. I’d like to thank Matthew for helping me to edit this post.

How not to write Dynamic SQL

Last time, I showed you three tips to debug your Dynamic SQL. Let’s take a step back. Let’s see what is a dynamic SQL query and how to use one to rewrite a stored procedure with optional parameters.

Dynamic SQL is a string with a query to execute. In a stored procedure with optional parameters, Dynamic SQL is used to build a string containing a query with only the comparisons and clauses for the parameters passed with a non-default value.

Without Dynamic SQL

Let’s go back to the stored procedure dbo.usp_SearchUsers from our previous post on debugging Dynamic SQL queries. This stored procedure finds StackOverflow users by display name or location or both.

Without Dynamic SQL, we end up with funny comparisons in the WHERE clause. First, we check if the optional parameters have value. To then, with an OR, add the right comparisons. Everything in a single statement.

CREATE OR ALTER PROC dbo.usp_SearchUsers
  @SearchDisplayName NVARCHAR(100) = NULL,
  @SearchLocation NVARCHAR(100) = NULL
AS
BEGIN
    
  SELECT TOP 100 *
  FROM dbo.Users u
  WHERE (@SearchDisplayName IS NULL OR DisplayName LIKE @SearchDisplayName)
    AND (@SearchLocation IS NULL OR Location LIKE @SearchLocation);
END
GO

Let’s run our stored procedure searching only by DisplayName and see its execution plan.

Notice SQL Server had to scan the DisplayName index and see the number of rows read.

Sometimes, we use the ISNULL() or COALESCE() functions instead of IS NULL. But, those are variations on the same theme.

The more optional parameters our stored procedure has, the worse our query gets. SQL Server will scan entire tables or indexes to satify our query.

With Dynamic SQL, the wrong way

Probably, we hear about Dynamic SQL somewhere on the Internet and we decide to use it.

Then, we write the next version of our stored procedure. Something like the one below.

CREATE OR ALTER PROC dbo.usp_SearchUsersWithWrongDynamicSQL
  @SearchDisplayName NVARCHAR(100) = NULL,
  @SearchLocation NVARCHAR(100) = NULL
AS
BEGIN
 
  DECLARE @StringToExecute NVARCHAR(4000);
    
  SET @StringToExecute = N'SELECT TOP 100 *
  FROM dbo.Users u
  WHERE (@SearchDisplayName IS NULL OR DisplayName LIKE @SearchDisplayName)
    AND (@SearchLocation IS NULL OR Location LIKE @SearchLocation);';

  EXEC sp_executesql @StringToExecute, 
    N'@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100)', 
    @SearchDisplayName, @SearchLocation;
END
GO

We moved the exact same query to a string and asked SQL Server to execute that string. That won’t make any difference between the execution plans of both versions. We only put makeup on the problem. Arggg!

With Dynamic SQL, the right way

With Dynamic SQL, we want to create smaller queries for the different set of parameters passed to our stored procedure.

We need to add only the comparisons and clauses for the parameters passed with non-default values.

Let’s rewrite the stored procedure to include the conditions to the WHERE based on the parameters passed.

CREATE OR ALTER PROC dbo.usp_SearchUsers_DynamicSQL
  @SearchDisplayName NVARCHAR(100) = NULL,
  @SearchLocation NVARCHAR(100) = NULL
AS
BEGIN
 
  DECLARE @StringToExecute NVARCHAR(4000);
    
  SET @StringToExecute = N'SELECT TOP 100 *
  FROM dbo.Users u
  WHERE 1 = 1';

  IF @SearchDisplayName IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' AND DisplayName LIKE @SearchDisplayName ';

  IF @SearchLocation IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' AND Location LIKE @SearchLocation ';

  EXEC sp_executesql @StringToExecute, 
    N'@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100)', 
    @SearchDisplayName, @SearchLocation;
END
GO

First, we created a @StringToExecute variable with the first part of the SELECT. We added 1 = 1 on the WHERE to easily add conditions in the next steps.

Instead of, 1 = 1 we can also use a common or required condition for all other set of parameters.

Then, notice the two IF statements. We added the conditions to the WHERE clause depending on the parameter passed.

After that, we executed the query inside the string with sp_executesql with the parameter declaration and the parameters themselves.

With Dynamic SQL, our stored procedure will generate one execution plan for each set of different parameters. That’s the point of using Dynamic SQL.

This time, SQL Server could seek on DisplayName instead of scanning it. That’s better.

Voilà! That’s how NOT to write a stored procedure with optional parameters with Dynamic SQL. Notice that to make things simple, we didn’t follow all the tips to make our Dynamic SQL easier to debug.

If you’re interested in more content about SQL and SQL Server, check my posts on Six SQL Server performance tuning tips and How to format your SQL queries.

Happy coding!

TIL: How to convert 2-digit year to 4-digit year in C#

Today I was working with credit cards and I needed to convert a 2-digit year to a 4-digit one in C#. The first thing that came to my mind was adding 2000 to it. But it didn’t feel right. It wouldn’t be a problem in hundreds of years, though.

To convert 2-digit year into a 4-digit year, use the ToFourDigitYear method inside your current culture’s calendar.

CultureInfo.CurrentCulture.Calendar.ToFourDigitYear(21)
// 2021

But, if you’re working with a string containing a date, create a custom CultureInfo instance and set the maximum year to 2099. After that, parse the string holding the date with the custom culture. Et voilà!

CultureInfo culture = new CultureInfo("en-US");
culture.Calendar.TwoDigitYearMax = 2099;

string dateString = "1 Jan 21";
DateTime.TryParse(dateString, culture, DateTimeStyles.None, out var result);
// true, 1/1/2021 12:00:00 AM

Sources: Convert a two digit year, Parse string dates with two digit year