Exporting with CTE query output in stored procedure

Hi, I have a stored procedure in which I am using 'with CTE'.  I need to be able to export the CTE query data into a csv file.  I can't seem to figure out how to do this.  Can someone help me with this? I have attached my stored procedure code.
USE [Marketing_Resumes]
/****** Object:  StoredProcedure [dbo].[spResumeCSVGeneration]    Script Date: 09/23/2010 10:15:52 ******/

ALTER PROCEDURE [dbo].[spResumeCSVGeneration] 
	-- Add the parameters for the stored procedure here
	@username varchar(50),
@profile varchar(50),
@filename varchar(50)

Declare @filepath  nvarchar(max)
Set @filepath = 'C:\' + @username + '_' + @filename + '.csv';

	With cte as(
select Username,Profile,employeeid,Employeename,Work_Summary,Accreditation_Affliation,PE_Cert,LEED_Cert,CPD_Cert,

row_number() over(partition by username,profile,employeeid order by sort_order) as rn
from Profiles_Employee_Resume)

select username,profile,employeeid,MAX(Employeename)as employeename,
MAX(Work_Summary) as worksummary,MAX(Accreditation_Affliation) as accreditation,
MAX(PE_Cert) as PEcert, Max(LEED_Cert) as LEEDcert,MAX(CPD_Cert) as CPDcert,MAX(RoleonProject) as RoleonProject,
MAX(ProjectName) as projectname,
max(case when rn=1 then project end) as project1,
max(case when rn=2 then project end) as project2,
max(case when rn=3 then project end) as project3,
max(case when rn=1 then title end) as title1,
max(case when rn=2 then title end) as title2,
max(case when rn=3 then title end) as title3,
max(case when rn=1 then location end) as projectlocation1,
max(case when rn=2 then location end) as projectlocation2,
max(case when rn=3 then location end) as projectlocation3,
max(case when rn=1 then ReferenceCompany end) as referencecompany1,
max(case when rn=2 then ReferenceCompany end) as referencecompany2,
max(case when rn=3 then ReferenceCompany end) as referencecompany3,
max(case when rn=1 then ReferenceTitle end) as referencetitle1,
max(case when rn=2 then ReferenceTitle end) as referencetitle2,
max(case when rn=3 then ReferenceTitle end) as referencetitle3,
max(case when rn=1 then ReferenceName end) as referencename1,
max(case when rn=2 then ReferenceName end) as referencename2,
max(case when rn=3 then ReferenceName end) as referencename3,
max(case when rn=1 then ReferenceEmail end) as referenceemail1,
max(case when rn=2 then ReferenceEmail end) as referenceemail2,
max(case when rn=3 then ReferenceEmail end) as referenceemail3,
max(case when rn=1 then ReferencePhone end) as referencephone1,
max(case when rn=2 then ReferencePhone end) as referencephone2,
max(case when rn=3 then ReferencePhone end) as referencephone3,
max(case when rn=1 then Completion_Year end) as educationyear1,
max(case when rn=2 then Completion_Year end) as educationyear2,
max(case when rn=3 then Completion_Year end) as educationyear3,
max(case when rn=1 then School_Facility end) as schoolfacility1,
max(case when rn=2 then School_Facility end) as schoolfacility2,
max(case when rn=3 then School_Facility end) as schoolfacility3,
max(case when rn=1 then Degree_Completed end) as degreecompleted1,
max(case when rn=2 then Degree_Completed end) as degreecompleted2,
max(case when rn=3 then Degree_Completed end) as degreeecompleted3

from CTE
where username=@username and profile = @profile
group by username, profile,EmployeeID
order by username, profile, employeename

Open in new window

Who is Participating?
imstac73Connect With a Mentor Author Commented:
I got help from MS support on this as we needed a solution pretty quickly.
Mark WillsTopic AdvisorCommented:
Can use the BCP facility to execute the SP and save the results to disk.

Probably wont be a true CSV in terms of encapsulating quoted columns etc, but can most definitely be a comma delimited file.

So, you might want to put in a few : replace(myfield,',',';')    (ie replacing commas with another character)

e.g. exec master..xp_cmdshell 'bcp "exec mydatabase..usp_my_procedure" queryout "c:\my_test_data.csv" -T -c -t, -CACP'

imstac73Author Commented:
Can I put the bcp execute code in a stored procedure or execute it from a website?  The end solution will be a button control in a website that generates the csv file.
Mark WillsTopic AdvisorCommented:
I do apologise - didnt see your reply. Very happy to hear you got it working...
imstac73Author Commented:
had to go MS support for help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.