Export Data from SqlServer to FlatFile along with Column Header using xp_cmdshell-BCP

This Export is achieved using xp_cmdshell commands.
Step1:

To enable xp_cmdshell in sqlserver execute the below query:

EXEC sp_configure 'show advanced options', 1
GO
reconfigure
EXEC sp_configure 'xp_cmdshell', 1
GO
reconfigure

Step2:
Configure other details to connect sqlserver in the stored procedure variable initialization part and

Execute below stored procedure which takes four input parameter(as per my requirement).

---INPUT PARAMETERS
@tObjectName AS nVARCHAR(255)--View/Table Name , “DBName..ViewName”
,@tColumnName AS nVARCHAR(255)--Column Name for Filtering
,@tDateValue AS DATETIME --Value for Filtering
,@tOutputFileName AS nVARCHAR(255)--Output File Name

--STORED PROCEDURE
--=============Testing==========================

--EXEC [Sproc_TPS_ExportDataToFlatFile] 'DBName..ViewName','View Column Name','Data Value','FileName'

--EXEC [Sproc_TPS_ExportDataToFlatFile] 'tlmain..view_tblcase','dcreatedat','12/27/2011','dump.txt'

--===============================================

--Note:Folder Path has to be created

GO

if exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[Sproc_TPS_ExportDataToFlatFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[Sproc_TPS_ExportDataToFlatFile]

GO

CREATE PROCEDURE [dbo].[Sproc_TPS_ExportDataToFlatFile]

@tObjectName AS nVARCHAR(255)

,@tColumnName AS nVARCHAR(255)

,@tDateValue AS DATETIME

,@tOutputFileName AS nVARCHAR(255)

AS

BEGIN

DECLARE @nObjectID AS INT

DECLARE @tsql AS varchar(8000)

DECLARE @tServerInstanceName AS nVarchar(255),@tUSerID AS nVarchar(255),@tPassword AS nVarchar(255)

,@tHeaderDelimiter AS nVarchar(10),@tViewName AS nVarchar(255),@tOutputFilePath AS nVarchar(MAX)

,@tTempFilePath AS nVarchar(MAX),@tTempString AS nVarchar(255),@tColumnList AS nVarchar(MAX)

,@tDelimiter AS nVarchar(10)

DECLARE @dtCurrentDate As DateTime

SET @tServerInstanceName='ServerName/InstanceName'

SET @tUSerID='UserID'

SET @tPassword='Password'

SET @tHeaderDelimiter='^|'-- ^ character is prefixed since pipe is an internal command for cmd

SET @tDelimiter='|'

SET @tOutputFilePath='C:\'+@tOutputFileName--Output Path

SET @tTempFilePath='C:\Temp.txt'--This file gets deleted at the end of export

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName AND COLUMN_NAME =@tColumnName)

BEGIN--2

SELECT @tColumnList=COALESCE(@tColumnList + @tHeaderDelimiter, '')+ COLUMN_NAME FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName

--print @tColumnList

SELECT @tsql ='echo ' + @tColumnList + ' > ' + @tOutputFilePath--Copy Header columns to output Flatfile

exec xp_cmdshell @tsql

--export Data to temp file

SELECT @tsql = 'bcp "SELECT * FROM '+@tObjectName+' WHERE '+@tColumnName+' > '''+CAST(@tDateValue AS nVarchar(255))+'''" queryout '+@tTempFilePath+' -c -t"'+@tDelimiter+'" -r\n -U'+@tUSerID+' -P'+@tPassword+' -S'+@tServerInstanceName

--print @tsql

exec xp_CmdShell @tsql

--Copy Data to Output File

SELECT @tsql ='type ' + @tTempFilePath+ ' >> ' + @tOutputFilePath

exec xp_cmdshell @tsql

--Delete Temp File

SELECT @tsql ='del '+@tTempFilePath

exec xp_cmdshell @tsql

END--2

END

GO