Solved

Exporting with CTE query output in stored procedure

Posted on 2010-09-23
5
841 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 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