CODEWITHSUNDEEP
sqlsql-server
ShortDive
ShortDive

Reputation: 177

SQL Server - Variables within STUFF Statement

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?

Upvotes: 4

Views: 1031

Answers (3)

ShortDive
ShortDive

Reputation: 177

I tried the answer of Gordon Linoff but that did not work as I got an error message probably relating to the INSERT statement that follows

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

    PRINT(@col_names_2)
    EXEC SP_EXECUTESQL @col_names_2

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

    EXEC(@SQL_5)

The error message says: Incorrect syntax near the keyword 'SELECT'. but the line for the error message is incorrect (Line 2 which is empty)

Upvotes: 1

ShortDive
ShortDive

Reputation: 177

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)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

A SQL statement does not start with STUFF(). It starts with SELECT.

I suspect you want:

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

PRINT(@col_names_2);
EXEC SP_EXECUTESQL @col_names_2;

This uses sp_executesql rather than exec -- it is much better for executing dynamic SQL. It also lets you assign the output value to a variable.

To assign the value to a variable:

DECLARE @sql NVARCHAR(MAX);
DECLARE @cols NVARCHAR(MAX);

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

PRINT(@sql);
EXEC SP_EXECUTESQL @sql,
                   N'@cols NVARCHAR(MAX) OUTPUT',
                   @cols = @cols;

PRINT(@cols);

Upvotes: 0

Related Questions