Solved

Exporting with CTE query output in stored procedure

Posted on 2010-09-23
5
839 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
  • 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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