Solved

Exporting with CTE query output in stored procedure

Posted on 2010-09-23
5
840 Views
Last Modified: 2012-05-10
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]
GO
/****** Object:  StoredProcedure [dbo].[spResumeCSVGeneration]    Script Date: 09/23/2010 10:15:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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


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,
RoleonProject,
ProjectName,Project,Title,Location,ReferenceTitle
      ,ReferenceName
      ,ReferenceCompany
      ,ReferenceEmail
      ,ReferencePhone
      ,Completion_Year
      ,School_Facility
      ,Degree_Completed,

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

0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33746652
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'


0
 

Author Comment

by:imstac73
ID: 33747477
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.
0
 

Accepted Solution

by:
imstac73 earned 0 total points
ID: 34065333
I got help from MS support on this as we needed a solution pretty quickly.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34066786
I do apologise - didnt see your reply. Very happy to hear you got it working...
0
 

Author Closing Comment

by:imstac73
ID: 34091359
had to go MS support for help
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 64
sql query help 15 67
sql server major issue  need help 2 82
SQL Server set parent recort in select with row number 8 28
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question