UDF Eliminates Concatenation Woes

You probably know how to concatenate multiple values from multiple rows of a table. This technique is useful when you need to provide multiple values in a single variable. For example, when you're calling a stored procedure, you might need to pass in multiple values in a single parameter, as Listing 2 shows.

However, when you use this type of concatenation, you're limited to 8,000 characters, which can be problematic in several ways. First, you might need many local variables to hold all the values in a table. Then, there's the problem of trying to figure out which variables are full and which variables still have room for more values. Another problem can occur when a table value is too large for a local variable. In such cases, you might need to split a table value across multiple local variables. You might even be forced into some kind of cursor or looping structure to address this particular problem.

If you've experienced any of these concatenation woes, have I got the solution for you: dbo.fn_AddToBuffer. Although dbo .fn_AddToBuffer won't eliminate unwanted belly fat or make you rich quick, it will eliminate concatenation problems and make your job easier because it uses any number of local variables as a buffer to work around the 8,000-character limitation. As Listing 3 shows, using this user-defined function (UDF) requires a bit more typing, but you end up with an elegant solution that's simple to read and works well. This UDF works on SQL Server 2005 and SQL Server 2000.

Here's how you use dbo.fn_AddToBuffer. The UDF needs three parameters. The first parameter is a varchar(8000) variable that represents the value you want to add to the "buffer" (i.e., the set of local variables). The second parameter is an integer that represents the starting buffer position for each local variable. The third parameter is an integer that represents the total buffer length prior to adding the current value. The UDF will evaluate just how many characters and which portion of the input value should be parsed and returned for each local buffer variable.

You can use dbo.fn_AddToBuffer for any number of local variables. In each local variable, it uses every available character position. I've found this function most useful when the called stored procedure performs dynamic SQL execution. When you use the EXEC statement for dynamic SQL, there's no practical limitation to how long the statement can be, so concatenating multiple 8,000-character strings together isn't a problem.

For example, suppose you have a SELECT statement that includes an IN clause. You can provide the IN values as parameters so that the dynamic SQL statement looks something like

EXEC (‘SELECT * FROM 
  InvoiceDetails WHERE 
  InvoiceNumber IN (‘ + @x1
  + @x2 + @x3 + @x4 
  + @x5 + ‘)' "

In the example in Listing 3, the values need to be delimited by a space, so I added a single space to the end of each InvoiceNumber. However, in other situations, you might not need a delimiter or you might need a different delimiter. Your values might not even be characters (e.g., they're INT values), or you might need your values properly quoted. You can determine what you need in the SELECT LIST of the subquery by using the CAST or CONVERT operators and adding your preferred delimiter and/or quotation marks.

If you often need to provide multiple values in a variable but find a single variable too limiting, give dbo.fn_AddToBuffer a try. This UDF isn't available at any store, and you won't find it advertised on TV. You can only find it on SQL Server Magazine's Web site.
—Lawrence Rogers