String formatting issue for creating the SQL query for BCP

I want to use some of the static data to the new BCP query and export the extracted data to CSV. The static data are generated as a result after running some BCP command earlier.

But I have been facing string formatting issue for creating the SQL query using those variables and generate a SQL command.

I simply want to use the following query in the BCP command:

 select @version, @TotalRecords

BCP query I have used is:

   DECLARE 
        @version varchar(10),
        @HeaderCmd VARCHAR(500),
        @StateCode varchar(2), 
        @v_Header_path varchar(255),
        @TotalRecords int;

    SET @StateCode = 'AL'  
    SET @version = 'ver4'  
    SET @TotalRecords = 20
    
    SET @v_Header_path ='c:csvtest' + @StateCode + '_header.txt'

   SELECT  @HeaderCmd = 'bcp ' 
                            +'"select '+ @version+ '" '
                            + ' queryout ' + @v_Header_path
                            + ' -c -t, -T' ;  

   SELECT  @HeaderCmd AS 'Command to execute';
   
   EXECUTE master..xp_cmdshell @HeaderCmd; 

The above command is generating the following sql command:

bcp "select ver4"  queryout c:csvtestAL_header.txt -c -t, -T

and giving the error as:

Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'ver4'.

I wanted to export the value of @version and @TotalRecords into the CSV file.

But I am stuck in a string formatting issue for creating the SQL query as the string.

Answer

Give the column a name, and output the value in quotes. Use consecutive single quotes to pass a quote to the string:

select @HeaderCmd = 
    'bcp ' +
    '"select ''' + @stateCode + ''' as stateCode, ''' + @version + ''' as version, ' + convert(varchar(10), @totalRecords) + ' as totalRecords" ' + 
    ' queryout ' + @v_Header_path + 
    ' -c -t, -T' ;

This should then output:

bcp "select 'AL' as stateCode, 'ver4' as version, 20 as totalRecords"  queryout c:csvtestAL_header.txt -c -t, -T