SQL BCP with column name

Question

I am trying to export my stored procedure to a .csv file using BCP. It does give me a output file in .CSV but it does not print column name. Below is the script. Please look at and let me know what i am missing

    DECLARE @command VARCHAR(4000)
    declare @fulldate varchar(30) = convert(varchar,GETDATE(),112)
    declare @year varchar(30) = left(@fulldate,4)
    declare @day  varchar(30) = right(@fulldate,2)
    declare @month varchar(30) = left(right(@fulldate,4),2)
    DECLARE @FileDirectory VARCHAR(1000) = 'c:\'
    DECLARE @FileName VARCHAR(255)= 'TestingDOC' + @month + '.' + @day + '.'      + @year  + '.txt'
    declare @attach varchar(1255) = @fileDirectory + @fileName

    SET @command = 'bcp "select * from ngprod.dbo.TEMP_PAS"'
        + ' queryout "' + @FileDirectory + @FileName + '"'
        + ' -c -t, -T -S'+ @@servername
    EXEC master..xp_cmdshell @command

Answer 1

After a lot of trail and error below is the answer on how to add column

First create a header.txt file (inside the header file add all of you header) for example if the header file need firstname, lastname etc

second paste the below query in your stored procedure

DECLARE @command VARCHAR(4000)

DECLARE @FileDirectory VARCHAR(1000) = 'c:\test\'

DECLARE @HeaderFile varchar(255) = 'Headers.txt'

DECLARE @FileName VARCHAR(255)

SET @FileName = 'TestFile_' + CONVERT(VARCHAR,GETDATE(),112)

SET @command = 'bcp "select * from TESTDB.dbo.TEST_Table"'

       + ' queryout "' + @FileDirectory + @FileName + '.txt"'

       + ' -c -q -t, -T  -S'+@@servername

EXEC master..xp_cmdshell @command

SET @command =  'copy "' + @FileDirectory + @HeaderFile + '"+"' + @FileDirectory + @FileName + '.txt"' +  ' "' + @FileDirectory + @filename + '.csv"'

EXEC master..xp_cmdshell @command

SET @command = 'del "' + @FileDirectory + @FileName + '.txt"'

EXEC master..xp_cmdshell @command