Export data from Microsoft SQL server to CSV file using stored procedure

Write following procedure and execute:-

But before that you need to enable xp_cmdshell
So you need to execute these for enable xp_cmdshell
/****************************/

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

/****************************/
 Then write procedure

Create PROC spExportToCSV

@FilePath varchar(100)

AS

BEGIN

DECLARE @SQL VARCHAR(500)

SET @SQL='SELECT * FROM [DB name].dbo.[Table Name]'

SET @SQL='SQLCMD -E -Q "SET NOCOUNT ON; '+@SQL+'" -o "'+@FilePath+'" -h-1 -s "," -W'

-- -h-1 for no header

-- if remove -h-1 then 1 header

--  -h1 then after every row there is header and so on

EXEC master..xp_cmdshell @SQL

END