Using T-SQL to Concatenate Data Into a Variable

Can I create in T-SQL a stored procedure that loops through rows and concatenates data into a variable, as I do in Visual Basic (VB)?

There are two basic ways to solve problems in T-SQL: row-by-row processing or set-based logic. Row-by-row processing is similar to what you might do in a procedural language such as VB. Usually, you implement row-by-row processing in T-SQL by using a T-SQL—based cursor. You can use a cursor to loop through a data set, concatenating data to an existing variable, but that approach is inefficient. Let's look at a simple example of how to use a cursor, then contrast the cursor to another approach to prove that cursors are an inefficient way to process data. (Experienced T-SQL developers can skip to the part of this answer in which I discuss Listing 3 to see an interesting T-SQL construct that you can use to concatenate data to a variable during a set-based operation.)

Listing 1 shows a simple example of how to use a cursor to calculate the sum of freight for all rows in the Northwind database's Orders table. This example calculates the correct value of 64942.6900 for the sum of the freight column across all rows in the Orders table, but it took 130ms to run on my laptop. Although 130ms might not sound like a lot of time, it's slow for such a small amount of data.

Listing 2 shows a better way to determine the sum of freight for all rows in the Orders table. The SUM() function is the traditional way to use SQL to solve this type of problem. The solution generates the same answer but is faster than the cursor method, and the code is clearly simpler. My laptop showed that the query ran in 0ms; however, because the SQL Server GETDATE() function is only accurate to within plus or minus 3ms, my test time was really between 0 and 3ms.

Listing 3 shows another set-based solution that generates the correct answer and runs in 0ms, but this solution looks like row-by-row processing. Many T-SQL developers don't realize you can use T-SQL this way. Only in rare cases would you want to use this approach instead of the built-in SUM() function because you'd be reinventing the wheel—using built-in functions is easy and simple. However, the solution that Listing 3 shows can be handy when you encounter a problem that a built-in function can't solve—such as the problem that Listing 4 demonstrates.

The code in Listing 4 shows how you can use Listing 3's approach to concatenate and pivot string data into a single variable. Listing 4's code sums data across an integer column, but it also concatenates the string value of stor_id for each row in the Pubs database's sales table into a comma-delimited string.