dynamically creating query in a loop I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows

WHILE (Data is in the table)
BEGIN
SELECT TOP 1 @result = result from table

@sql = @sql + ', ' + @result

END


If I look at @sql in each loop I see the expected results, but when the loop is finished @sql is empty. Can someone explain why?
Assuming your pseudo-code is a good approximation of what your actual code is doing, you'd need something like:

DECLARE @sql NVARCHAR(MAX);
SET @sql = '';
WHILE (Data is in the table)
BEGIN
    SELECT TOP 1 @result = result from table;

    @sql = @sql + ', ' + @result;

END


If you don't initialize @sql to a zero-length string prior to the loop, SQL Server will attempt to append @result to a NULL string, which results in a NULL string, hence the @sql at the end of the loop will appear empty.