Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Exporting with CTE query output in stored procedure

Posted on 2010-09-23
5
Medium Priority
?
842 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

730 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