Passing output from dynamic SQL

Question

Currently I have a working version of a dynamic SQL query without any variables except one (@ColumnHeader). And am able to get the desired result being a collection of column names in one line separated by comma.

select @ColumnHeader = COALESCE(@ColumnHeader+',','') + '''' + column_name + '''' 
from databaseName.Information_Schema.Columns
where table_name = 'Dates'

I am trying to add variables for Database_information_schema and TableName.

DECLARE @ColumnHeader varchar(8000) 
DECLARE @Database_Information_SchemaColumns varchar(8000) = 'DatabaseName2.Information_Schema.Columns'
DECLARE @TableName varchar(8000) = 'dates'

DECLARE @sqlQuery as nvarchar(max) = 'Select ' + @ColumnHeader + '= COALESCE(' + @ColumnHeader+ +''','','''')+ ''''''''+column_name+'''''''' from ' + @Database_Information_SchemaColumns + 'where table_name = '''+ @TableName + ''''

Print @sqlQuery

EXEC sp_executesql @sqlQuery;

I am getting Null values and am not sure whats wrong here.

Answer 1

What follows is some working code. I explain the changes required:

1. You are trying to assign a parameter using dynamic SQL, therefore the parameter needs to be part of the dynamic SQL string, not concatenated with it.
2. To assign a parameter using dynamic SQL you have to pass it in/out of sp_executesql because the context the dynamic SQL is running under cannot see the parameters declared.
3. While this is not necessary, I have renamed the internal parameter so that its clear which parameter belongs in which scope. However both could use the same name if desired.
4. You were missing a space before your where.
5. I recommend using varchar(max) and nvarchar(max) as there is no need to risk running into the 8k limit.
6. Use quotename for any database, schema, table or column names to protect against injection.
7. Use the sysname datatype where a system name is being stored
8. Split all system names into parts to allow the use of quotename

    DECLARE @ColumnHeader varchar(max) 
        , @Database_Information_Database sysname = 'DatabaseName2'
        , @Database_Information_Schema sysname = 'Information_Schema'
        , @Database_Information_Columns sysname = 'Columns'
        , @TableName sysname = 'dates';

    DECLARE @sqlQuery nvarchar(max) = 'select @ColumnHeaderInteral = COALESCE(@ColumnHeaderInteral,'','','''') + '''''''' + column_name + '''''''' from '
        + quotename(@Database_Information_Database) + '.'
        + quotename(@Database_Information_Schema) + '.'
        + quotename(@Database_Information_Columns)
        + ' where quotename(table_name) = ''' + quotename(@TableName) + '''';

    PRINT @sqlQuery;

    EXEC sp_executesql @sqlQuery, N'@ColumnHeaderInteral varchar(max) output', @ColumnHeaderInteral = @ColumnHeader out;

    SELECT @ColumnHeader;

Official Documentation