A quick guide to LINQ with examples

Today a friend asked me about LINQ. I guess she was studying for a technical interview. So, dear Alice, this is what LINQ is and these are the most common LINQ methods with examples in C#. All you need to know in 15 minutes or less.

Language-Integrated Query (LINQ) is the declarative way of working with collections in C#. LINQ can be used with databases and xml files too. The most common LINQ methods are: Where, Select, Any GroupBy and FirstOrDefault. LINQ can be found as an API syntax, extensions methods on the IEnumerable type, or as a language-level query syntax, a SQL-like syntax.

LINQ is declarative

LINQ is declarative. It means you write your code stating the results you want, instead of doing every step to get those results. For example, you write code to “filter a collection based on a condition”. Instead of writing code to “grab an element, check if it satisfies a condition, then move to the next element, check again…“, etc.

LINQ is a better alternative to query collections using for, foreach or any other loop. Because, with LINQ you can write more expressive and compact code.

Our first example: Movies

Let’s see an example. Let’s start with the collection of movies we have watched. We have a Movie class with a name, release year and rating. Let’s find our favorite movies, the ones with rating greater than 4.5.

Waiting at a cinema before a movie starts
Photo by Erik Witsoe on Unsplash

A console program to print our favorite movies looks like the next code listing. Notice the foreach loop and the comparison using an if statement to look for ratings greater than 4.5.

using System;
using System.Collections.Generic;

namespace QuickLinqGuide
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var movies = new List<Movie>
            {
                new Movie("Titanic", 1998, 4.5f),
                new Movie("The Fifth Element", 1997, 4.6f),
                new Movie("Terminator 2", 1991, 4.7f),
                new Movie("Avatar", 2009, 5),
                new Movie("Platoon", 1986, 4),
                new Movie("My Neighbor Totoro", 1988, 5)
            };

            var favorites = new List<Movie>();
            foreach (var movie in movies)
            {
                if (movie.Rating > 4.5)
                {
                    favorites.Add(movie);
                }
            }

            foreach (var favorite in favorites)
            {
                Console.WriteLine($"{favorite.Name}: [{favorite.Rating}]");
            }

            Console.ReadKey();
        }
    }

    internal class Movie
    {
        public Movie(string name, int releaseYear, float rating)
        {
            Name = name;
            ReleaseYear = releaseYear;
            Rating = rating;
        }

        public string Name { get; set; }
        public int ReleaseYear { get; set; }
        public float Rating { get; set; }
    }
}

For our sample movies, the above program will print the next four movies.

The Fifth Element: [4.6]
Terminator 2: [4.7]
Avatar: [5]
My Neighbor Totoro: [5]

Change the example to use your own movies and see which ones are your favorites!

Our first LINQ method: Where

LINQ methods are extension methods on the IEnumerable type. This type represents objects we can loop through. Like, arrays, lists, dictionaries, among others.

In case you missed it…You can add methods to a type without modifying it with extension methods. They are static methods defined outside the declaration a type. But, they look like normal methods when you use them.

To work with LINQ, you need to be comfortable with delegates and lambda functions. A lambda function is a method with only the parameters and the body. To learn more about delegates and lambda functions, check my post What the Func, Action?.

Now, to the actual example. To start using LINQ methods, let’s add the using statement using System.Linq.

Next, we want to filter our list of movies to keep only the ones with rating greater than 4.5. The LINQ method to filter collections is Where. Where returns a new collection with all the elements that meet a condition.

Let’s replace the first foreach statement from our example with the Where method. And use the condition inside the if statement as the filter condition for the Where method. Our example looks like this:

using System;
using System.Collections.Generic;
using System.Linq;

namespace QuickLinqGuide
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var movies = new List<Movie>
            {
                new Movie("Titanic", 1998, 4.5f),
                new Movie("The Fifth Element", 1995, 4.6f),
                new Movie("Terminator 2", 1999, 4.7f),
                new Movie("Avatar", 2010, 5),
                new Movie("Platoon", 1986, 4),
                new Movie("My Neighbor Totoro", 1988, 5)
            };

            var favorites = movies.Where(movie => movie.Rating > 4.5);

            foreach (var favorite in favorites)
            {
                Console.WriteLine($"{favorite.Name}: [{favorite.Rating}]");
            }

            Console.ReadKey();
        }
    }

    // The Movie class remains the same
}

We replaced the foreach and if statements with a single line of code:

var favorites = movies.Where(movie => movie.Rating > 4.5);

More compact, isn’t it? Also, we turned the condition inside the if statement into a lambda function.

LINQ methods don’t change the original collection. They return a result without modifying the original collection. From our example, when we used the Where method, it returned a new collection. It didn’t remove any elements from the original movies list.

Most common LINQ methods

So far, we have seen only one LINQ method, Where. Let’s see other common methods.

Select

With Select, you can transform every element of a collection. It applies a function on every element.

Let’s find only the names of our favorite movies.

var favorites = movies.Where(movie => movie.Rating > 4.5)
                      .Select(movie => movie.Name);

foreach (var name in favorites)
{
    Console.WriteLine(name);
}

// The Fifth Element
// Terminator 2
// Avatar
// My Neighbor Totoro

Notice, how this time we have nested two LINQ methods. The result from Where will be the input of Select.

For more readability, we often align the nested LINQ methods vertically by the (.) dot

Any

The Any method check if a collection is empty. Also, it checks if a collection has at least one element matching a condition. It returns either true or false. It doesn’t return a new collection.

Let’s see if we have watched movies with a low rating.

var hasAnyMovies = movies.Any();
// true

var hasBadMovies = movies.Any(movie => movie.Rating < 2);
// false

GroupBy

GroupBy groups the elements of a collection based on a key. It returns a collection of “groups” or “buckets” organized by a key.

Let’s group our movies by rating.

var groupedByRating = movies.GroupBy(movie => movie.Rating);

foreach (var group in groupedByRating)
{
    Console.WriteLine($"Rating: {group.Key}");

    foreach (var movie in group)
    {
        Console.WriteLine($"{movie.Name}");
    }
    Console.WriteLine();
}

This will be the output.

Rating: 4.5
Titanic

Rating: 4.6
The Fifth Element

Rating: 4.7
Terminator 2

Rating: 5
Avatar
My Neighbor Totoro

Rating: 4
Platoon

Also, GroupBy allows you to transform each group. This time, let’s count the movies with the same rating.

var groupedByRating = movies.GroupBy(movie => movie.Rating,
                                    (rating, movies) => new { Rating = rating, Count = movies.Count() });

foreach (var group in groupedByRating)
{
    Console.WriteLine($"{group.Rating}: [{group.Count}]");
}

Notice the second parameter of the GroupBy. It’s a Func with the key and the elements of each group. We also used an anonymous object new { Rating=..., Count=... }. It’s like a regular object, but we didn’t specify a name.

And this is the output of counting movies by rating.

4.5: [1]
4.6: [1]
4.7: [1]
5: [2]
4: [1]

First/FirstOrDefault

First and FirstOrDefault return the first element in a collection. First throws an exception if the collection is empty. Unlike First, FirstOrDefault returns a default value if the collection is empty.

Let’s find the oldest movie we have watched.

var oldest = movies.OrderBy(movie => movie.ReleaseYear)
                   .First();

// Platoon

This time, we used the OrderBy to sort the movies collection by release year. Two examples for the price of one!

In the same spirit of First and FirstOrDefault, you have Last and LastOrDefault. But, they return the last element instead of the first one.

Recently, I learned about the DefaultIfEmpty method. It returns a new collection with a default value if the given collection is empty. Good to know!

Cheatsheet

There are more LINQ methods than the ones we’ve seen so far. These are some of them.

Method Function
Where Filter a collection
Select Transform every element of a collection
Any Check if a collection is empty
Count Count all elements of a collection
Distinct Find the unique elements of a collection
GroupBy Group the elements of a collection based on a key
OrderBy Sort a collection based on a key
First Find the first element of a collection. Throw if the collection is empty
FirstOrDefault Same as First, but it returns a default value if it’s empty
Last Find the last element of a collection. Throw if the collection is empty
LastOrDefault It returns a default value if it’s empty, instead
Single Find only one element in a collection matching a condition. Throw, otherwise
SingleOrDefault It returns a default value if there isn’t one matching element, instead
Take Pick the first n consecutive elements of a collection
TakeWhile Pick the first consecutive elements that satisfy a condition
Skip Return a collection without the first n consecutive elements
SkipWhile Return a collection without the first consecutive elements that satisfy a condition
Sum Sum the elements of a collection
Min, Max Find the smallest and largest element of a collection
ToDictionary Convert a collection into a dictionary

Query syntax: A matter of taste

Up to this point, we have seen LINQ as extension methods. But, you can find LINQ as language-level query syntax too.

This is the same example to find our favorite movies using language-level query syntax.

var bestOfAll = from movie in movies
                where movie.Rating > 4.5
                select movie;

It looks like SQL, isn’t it? And, this is the same code using extension methods. We’ve seen this before.

var bestOfAll = movies.Where(movie => movie.Rating > 4.5);

Which LINQ syntax should you use? Prefer the syntax used in your current codebase. If your code uses extensions methods on IEnumerable, continue to do that.

Popcorn
Photo by Christian Wiediger on Unsplash

But, there is one advantage of using query syntax over extension methods. You can create intermediate variables with the let keyword.

Let’s find all files inside our Desktop folder larger than 10MB. And, let’s use let to create a variable.

using System;
using System.IO;
using System.Linq;

namespace QuickLinqGuide
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            var desktop = new DirectoryInfo(desktopPath);

            var largeFiles = from file in desktop.GetFiles()
                             let sizeInMb = file.Length * 1024 * 1024
                             where sizeInMb > 10
                             select file.Name;

            foreach (var file in largeFiles)
            {
                Console.WriteLine(file);
            }

            Console.ReadKey();
        }
    }
}

From the above example, the file size is in bytes. Then, notice how we declared an intermediate variable. Like this let sizeInMb = file.Length * 1024 * 1024.

Gotchas

Count vs Any

Always prefer Any over Count to check if a collection has elements or if it has elements that meet a condition.

Do movies.Any() instead of movies.Count() > 0.

Where follow by Any

You can use a condition with Any instead of filtering first with Where to then use Any.

Do

movies.Any(movie => movie.Rating == 5)

Instead of

movies.Where(movie => movie.Rating == 5).Any()

The same applies to the Where method followed by FirstOrDefault, Count or any other method that receives a filter condition.

FirstOrDefault, LastOrDefault and SingleOrDefault

Make sure to always check if you have a result when working with FirstOrDefault, LastOrDefault and SingleOrDefault. In case there isn’t one, you will get the default value of the collection type.

private static void Main(string[] args)
{
    var movies = new List<Movie>
    {
        new Movie("Titanic", 1998, 4.5f),
        new Movie("The Fifth Element", 1995, 4.6f),
        new Movie("Terminator 2", 1999, 4.7f),
        new Movie("Avatar", 2010, 5),
        new Movie("Platoon", 1986, 4),
        new Movie("My Neighbor Totoro", 1988, 5)
    };

    var worst = movies.FirstOrDefault(movie => movie.Rating < 2);

    Console.WriteLine($"{worst.Name}: [{worst.Rating}]");
    //                  ^^^^^^^^^^^^ 
    // System.NullReferenceException: 'Object reference not set to an instance of an object.'
    //
    // worst was null.

    Console.ReadKey();
}

For objects, the default value would be a null reference. And you know what happens when you try to access a property or method on a null reference?…Yes, It thows NullReferenceException.

Conclusion

Voilà! That’s it, Alice. That’s all you need to know to start working with LINQ in your code in 15 minutes or less. There’s also this project MoreLINQ with more extension methods, like CountBy, DistinctBy, MinBy and MaxBy. With LINQ you can write more compact and expressive code. The next time you need to write logic using loops, give LINQ a try!

Happy LINQ time!

TIL: Three differences between TRUNCATE and DELETE

These days I learned three differences between TRUNCATE and DELETE statements in SQL Server. Let me share them with you.

Both DELETE and TRUNCATE remove records from a table. But, DELETE accepts a WHERE condition to only remove some records, TRUNCATE doesn’t. Also, DELETE doesn’t reset identity columns to its initial value, but TRUNCATE does. And, DELETE fire triggers, TRUNCATE doesn’t.

To see these three differences in action, let’s create a sample database with a Movies table. It only contains an auto-incremented id, a movie title and an score.

CREATE DATABASE DeleteVsTruncate
GO

USE DeleteVsTruncate
GO

CREATE TABLE dbo.Movies(
    Id INT PRIMARY KEY IDENTITY,
    Name VARCHAR(255) NOT NULL,
    Score INT
)
GO

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO

WHERE clause

The first difference is about the WHERE clause. One one hand, DELETE accept a WHERE clause to only delete some records from a table. But, TRUNCATE doesn’t. It deletes all records from a table. If you try to add a WHERE clause with TRUNCATE, you get “Incorrect syntax near the keyword ‘WHERE’“.

SELECT * FROM dbo.Movies
GO

DELETE FROM dbo.Movies WHERE Name = 'Armageddon'
GO
TRUNCATE TABLE dbo.Movies WHERE Name = 'Armageddon'
                          ~~~~~ Incorrect syntax near the keyword 'WHERE'

Identity columns

An identity column is a column with automatic incremented values. It’s used to create key values in tables.

Values for identity columns start from a “seed” value and increase by an “increment” value. You can use any number as seed and any positive or negative number as increment. By default, if you don’t use any seed or increment, it starts from 1 and increments by 1. IDENTITY = IDENTITY(1, 1)

DELETE statements don’t reset identity columns. It means new rows will have the next value in the identity columns. But, TRUNCATE does reset identity columns. The next new row will have the seed in the identity column.

Let’s delete all movies from our sample table and see the Id columns for the new movies.

SELECT * FROM dbo.Movies
GO
DELETE FROM dbo.Movies
GO
INSERT INTO dbo.Movies
VALUES
    ('Avatar', 5)
GO
SELECT * FROM dbo.Movies
GO
DELETE doesn't reset identity columns

Notice how ‘Avatar’ still has the Id = 4 after deleting all movies.

Now, let’s see how TRUNCATE resets the identity column. This time, let’s use TRUNCATE instead of DELETE.

SELECT * FROM dbo.Movies
GO
TRUNCATE TABLE dbo.Movies
GO
INSERT INTO dbo.Movies
VALUES
    ('Platoon', 4)
GO
SELECT * FROM dbo.Movies
GO
TRUNCATE resets identity columns

Notice the Id of ‘Platoon’. It’s 1 again. When we created our Movies table, we used the default seed and increment.

Triggers

A triggers is an special type of store procedure that runs when a given action has happened at the database or table level. For example, you can run a custom action inside a trigger after INSERT, DELETE or UPDATE to a table.

When you work with triggers, you have two virtual tables: INSERTED, DELETED. These tables hold the values inserted or deleted in the statement that started the trigger in the first place.

Now, back to the differences between TRUNCATE and DELETE. DELETE fires triggers, TRUNCATE doesn’t.

Let’s create a trigger that shows the deleted values. It uses the DELETED table.

CREATE OR ALTER TRIGGER dbo.PrintDeletedMovies
ON dbo.Movies
AFTER DELETE
AS
BEGIN
    SELECT Id 'Deleted Id', Name 'Deleted Name', Score 'Deleted Score'
    FROM DELETED;
END

Now, let’s delete our movies with DELETE and TRUNCATE to see what happens. First, let’s add some new movies and let’s use the DELETE.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO
DELETE FROM dbo.Movies
GO
DELETE fires triggers

Notice the results tab with our three sample movies. Now, let’s use TRUNCATE.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO
TRUNCATE TABLE dbo.Movies
GO
TRUNCATE doesn't fire triggers

Now, after truncating the table, we only see in the messages tab the number of rows affected. No movies shown.

Bonus: Rollback a TRUNCATE

We can rollback a TRUNCATE operation. To see this, let’s add our three movies and use a ROLLBACK with some SELECT’s in between.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO

BEGIN TRAN
    TRUNCATE TABLE dbo.Movies;

    SELECT * FROM dbo.Movies;
ROLLBACK
GO

SELECT * FROM dbo.Movies;
GO
You can rollback a TRUNCATE

Notice, the two results. The one inside the transaction, before the rollback, is empty. And the last one, after the rollback, with our three movies.

Voilà! Those are three differences between DELETE and TRUNCATE.

A case of primitive obsession. A real example in C#

These days I was working with Stripe API to take payments. And I found a case of primitive obsession. Keep reading to learn how to get rid of it.

Primitive obsession is when developers choose primitive types (strings, integers, decimals) to represent entities of the business domain. For example, plain strings for usernames or decimals for currencies. To solve this code smell, create classes to model the business entities. And, use those classes to enforce the appropriate business rules.

Using Stripe API

Stripe API uses units to represent amounts. All amounts are multiplied by 100. This is 1USD = 100 units. Also, you can only use amounts between $0.50 USD and $999,999.99 USD. This isn’t the case for all currencies, but let’s keep it simple. For more information, check Stripe documentation for currencies.

The codebase I was working with used two extension methods on the decimal type to convert between amounts and units. Those two method were something like ToUnits and ToAmount. But, besides variable names, there wasn’t anything preventing to use a decimal instead of Stripe units. It was the same decimal type for both concepts. Anyone could forget to convert things and charge someone’s credit card more than expected. Arggg!

A case of primitive obsession
Photo by rupixen.com on Unsplash

Getting rid of primitive obsession

An alias

As an alternative to encode units of measure on names, we can use a type alias. Let’s declare using Unit = System.Decimal and change the correct parameters to use Unit. But, the compiler won’t warn if we pass decimal instead of Unit. See the snippet below.

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace GettingRidOfPrimitiveObsession
{
    using Unit = System.Decimal;

    [TestClass]
    public class ConvertBetweenAmountAndUnits
    {
        [TestMethod]
        public void UseTypeAlias()
        {
            decimal amount = 100;
            Unit chargeAmount = amount.ToUnit();

            var paymentService = new PaymentService();
            paymentService.Pay(chargeAmount);

            paymentService.Pay(amount);
            // ^^^^ It compiles
        }
    }

    public class PaymentService
    {
        public void Pay(Unit amountToCharge)
        {
            // Magic goes here
        }
    }

    public static class DecimalExtensions
    {
        public static Unit ToUnits(this decimal d)
            => d * 100;
    }
}

Using a type alias is more expressive than encoding the unit of measure in variable names and parameters. But, it doesn’t force us to use one type instead of the other. Let’s try a better alternative.

A class

Now, let’s create a Unit class and pass it around instead of decimal. In the constructor, we can check if the input amount is inside the bounds. Also, let’s use a method to convert units back to normal amounts.

public class Unit
{
    internal decimal Value { get; }

    private Unit(decimal d)
    {
        if (d < 0.5m || d > 999_999.99m)
        {
            throw new ArgumentException("Amount outside of bounds");
        }

        Value = d * 100m;
    }

    public static Unit FromAmount(decimal d)
      => new Unit(d);

    public decimal ToAmount()
        => Value / 100m;
}

After using a class instead of an alias, the compiler will warn us if we switch the two types by mistake. And, it’s clear from a method signature if it works with amounts or units.

If needed, we can overload the + and - operators to make sure we’re not adding oranges and apples.

[TestMethod]
public void UseAType()
{
    decimal amount = 100;
    Unit chargeAmount = Unit.FromAmount(amount);

    var paymentService = new PaymentService();
    paymentService.Pay(chargeAmount);

    // paymentService.Pay(amount);
    // ^^^^ cannot convert from 'decimal' to 'GettingRidOfPrimitiveObsession.Unit'
}

Voilà! That’s how we can get rid of primitive obsession. A type alias was more expressive than encoding units of measure on names. But, a class was a better alternative. By the way, F# supports unit of measures to variables. And, the compiler will warn you if you forget to use the right unit of measure.

Looking for more content on C#? Check my post series on C# idioms and my C# definitive guide

Happy coding!

TIL: Always Use a Culture When Parsing Numeric Strings in C#

This week, I reinstalled the operating system of my computer. The new version uses Spanish, instead of English. After that, lots of unit tests started to break in one of my projects. The broken tests verified the formatting of currencies. Some of the these tests looked like the one below.

[TestMethod]
public void ToCurrency_IntegerAmount_FormatsAmountWithTwoDecimalPlaces()
{
    decimal value = 10M;
    var result = value.ToCurrency();

    Assert.AreEqual("10.00", result);
}

And, this was the ToCurrency method.

public static string ToCurrency(this decimal amount)
{
    return amount.ToString("0.00");
}

The ToCurrency method didn’t specify any culture. It used the user current culture. And, the test expected . as the separator for decimal places. It wasn’t the case for the culture I was using. The separator for my locale was ,. That’s why those tests failed.

To have a set of always passing unit tests, use a default culture when parsing numeric strings. Instead of using a plain Parse and ToString on decimals, add a default culture with these two methods. As an alternative, you could wrap your tests in a helper method to set the appropriate culture to run your tests.

For example, you can create ToCurrency and FromCurrency methods like this:

public static class FromattingExtensions
{
    private static CultureInfo DefaultCulture
        = new CultureInfo("en-US");

    public static string ToCurrency(this decimal amount)
    {
        return amount.ToString("0.00", DefaultCulture);
    }

    public static decimal FromCurrency(this string amount)
    {
        return decimal.Parse(amount, DefaultCulture);
    }
}

Voilà! That’s what I learned after reinstalling the operating system of my computer and running some unit tests. For other tips on writing unit tests, you can check How to write good unit tests

All tests turned green!

TIL: Three Tricks to Debug Your Dynamic SQL Queries

Format your dynamic SQL queries for more readability

To read your dynamic queries stored in the plan cache, make sure to insert new lines when appropriate. Use a variable for the line endings. For example, DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10).

Also, to identify the source of a dynamic query, add as a comment the name of the store procedure generating it. But, don’t use inside that comment a timestamp or any other dynamic text. Otherwise, you will end up with almost identical entries in the plan cache.

Add a parameter to print the generated query

To debug the generated dynamic query, add a parameter to print it. Also, you can add a second debugging parameter to avoid executing the query. For example, you can name these two parameters, @Debug_PrintQuery and @Debug_ExecuteQuery, respectively.

Change the casing of variables and keywords inside your dynamic SQL

To distinguish errors between the actual SQL query and the dynamic query, change the casing keywords and variables inside your the dynamic query.

Example

In the store procedure dbo.usp_SearchUsers below, notice the use of the variable @crlf to insert line breaks and the comment /* usp_SearchUsers */ to identify the source of the query.

Also, check the two debugging parameters: @Debug_PrintQuery and @Debug_ExecuteQuery. And, finally, see how the casing is different inside the dynamic SQL.

CREATE OR ALTER PROC dbo.usp_SearchUsers
  @SearchDisplayName NVARCHAR(100) = NULL,
  @SearchLocation NVARCHAR(100) = NULL
  @Debug_PrintQuery TINYINT = 0,
  @Debug_ExecuteQuery TINYINT = 1 AS
BEGIN
  DECLARE @StringToExecute NVARCHAR(4000);
  DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10);
    
  SET @StringToExecute = @crlf + N'/* usp_SearchUsers */' + N'select * from dbo.Users u where 1 = 1 ' + @crlf;

  IF @SearchDisplayName IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' and DisplayName like @searchdisplayName ' + @crlf;

  IF @SearchLocation IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' and Location like @searchlocation ' + @crlf;

  IF @Debug_PrintQuery = 1
    PRINT @StringToExecute

  IF @Debug_ExecuteQuery = 1
    EXEC sp_executesql @StringToExecute, 
      N'@searchdisplayName nvarchar(100), @searchlocation nvarchar(100)', 
      @SearchDisplayName, @SearchLocation, @SearchReputation;
END
GO

Source: Dynamic SQL Pro Tips