How To Export Data To the .csv file using Sql server Stored Procedure.

you can use query Like given below

declare @sql varchar(8000)
select @sql = 'bcp "select * from EmailVarification..tblTransaction" queryout c:\bcp\Tom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

in this query EmailVarification is DB name and tblTransaction is table name.
for execute this query you have to set path for write file.
currently in this query you have to create a "bcp" folder in "C:\ "Drive .

Thanks

Yes, it is possible to do it from a stored procedure. We currently have something like this working in our production environment.

Use T-SQL

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

But, there's a couple of caveats:

1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.

2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

Hi,

Refer the following Links

Creating CSV Files Using BCP and Stored Procedures[^]

Exporting a csv file via a stored procedure[^]

Regards,
GVPrabu

Sorry, it is not possible to write data to disk using Stored Procedure. You can use SQL Server Export Import Wizard to generate the file and save it as a SSIS package for further use.