SQL Server - Variables within STUFF Statement

Question

I'm currently working with SQL Server 2008 R2 and I'm having difficulties with the STUFF-statement.

The following statement works fine:

SET @col_names_2 = STUFF((
                                        SELECT ',' + [COLUMN_NAME]
                                        FROM test.dbo.common_columns
                                        FOR XML PATH('')),1,1,'')

But I want to use variables within the STUFF-statement likes this:

SET @col_names_2 = STUFF((
                                   SELECT ',' + [COLUMN_NAME]
                                   FROM '+@DBO_Database+'.'+@DBO_Schema+'.'+@common_cols+'
                                   FOR XML PATH('')),1,1,'')

I already tried several ways, for example like this:

SET @col_names_2 = 'STUFF((
                                   SELECT '','' + [COLUMN_NAME]
                                   FROM '+@DBO_Database+'.'+@DBO_Schema+'.'+@common_cols+'
                                   FOR XML PATH('''')),1,1,'''')'                                            

PRINT(@col_names_2)
EXEC(@col_names_2)

The PRINT statement gives me the following result which looks good:

STUFF((
                                           SELECT ',' + [COLUMN_NAME]
                                           FROM test.dbo.common_columns
                                           FOR XML PATH('')),1,1,'')

How can I assign the output of this STUFF statement to a variable? I already tried it with a SET statement within the batch statement but this did not work either. Currently I get the following error message:

Incorrect syntax near the keyword 'FOR'.

What am I doing wrong?

Answer 1

I think I solved it, especially thanks to the comment of lad2025. Still thanks to everyone else who answered.

The following solution works for me:

    SET @col_names_2 = 'SET @col_names_5 = STUFF((
                                    SELECT '','' + [COLUMN_NAME]
                                    FROM '+@DBO_Database+'.'+@DBO_Schema+'.'+@common_cols+'
                                    FOR XML PATH('''')),1,1,'''')'
    PRINT(@col_names_2)
    --EXEC(@col_names_2)

    EXECUTE sp_executesql @col_names_2, N'@col_names_5 nvarchar(max) OUTPUT', @col_names_5 OUTPUT
    PRINT @col_names_5

    SET @SQL_5 =        'INSERT INTO [Test].[clean].[cleantable]
                        SELECT '+@col_names_5+'
                        FROM [Test].[dbo].[dbotable]'

    EXEC(@SQL_5)