Efficient Conversion of SQL Server Result Sets to Single Strings

Dec 01, 2025 · Programming · 11 views · 7.8

Keywords: SQL Server | T-SQL | String Concatenation | STUFF | FOR XML PATH

Abstract: This article provides a comprehensive guide on converting SQL Server query results into a single string, such as comma-separated values. It focuses on the optimal method using STUFF and FOR XML PATH, with an alternative approach for comparison, aimed at T-SQL developers.

Introduction

In SQL Server development, it is common to need to convert a result set, often from a SELECT query, into a single string. For instance, when dealing with stored procedures that require an output parameter as a concatenated list of values, such as comma-separated student IDs. This article delves into efficient methods to achieve this conversion.

Using STUFF and FOR XML PATH

The most efficient method involves using the STUFF function in combination with FOR XML PATH. This approach concatenates values into a string and removes the leading comma. The core code snippet is as follows:

DECLARE @result NVARCHAR(MAX)
SELECT @result = STUFF(
                        (   SELECT ',' + CONVERT(NVARCHAR(20), StudentId) 
                            FROM Student 
                            WHERE condition = 'xyz' 
                            FOR XML PATH('')
                        )
                        , 1
                        , 1
                        '')

In this code, the inner SELECT statement uses FOR XML PATH('') to concatenate the StudentId values with commas. The STUFF function then removes the first character (position 1, length 1) by replacing it with an empty string, resulting in a clean comma-separated string stored in the @result variable.

Alternative Method with Variable Concatenation

Another approach is to use a variable to accumulate the string, as shown in the following code:

DECLARE @result VARCHAR(1000)
SELECT @result = ISNULL(@result, '') + StudentId + ',' FROM Student WHERE condition = 'xyz'
SELECT SUBSTRING(@result, 0, LEN(@result))  -- Trim the extra comma at the end

This method iterates through the result set, appending each StudentId followed by a comma to the @result variable. The SUBSTRING function is used to remove the trailing comma. While simpler, this method may be less efficient for large datasets and requires handling NULL values with ISNULL.

Comparison and Recommendations

The STUFF and FOR XML PATH method is generally preferred due to its performance and robustness. It handles NULL values implicitly when CONVERT is used and ensures proper string formatting. The variable concatenation method is easier to understand but can have performance issues and requires careful management of string lengths and NULLs. For most scenarios, especially in production, the first method is recommended.

Copyright Notice: All rights in this article are reserved by the operators of DevGex. Reasonable sharing and citation are welcome; any reproduction, excerpting, or re-publication without prior permission is prohibited.