Output of a Stored Procedure to a .csv file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Output of a Stored Procedure to a .csv file

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-11-02 : 11:36:08

I have the following code (below) to create a SP which gets called by Crystal reports to display a report on a screen.

What I need help with .... I need to find out of there is a way in T-SQL or SQL Server to execute this Store Procedure and have the output go to a .csv file????

CREATE PROCEDURE SP_Program_Schedule
@Searchtext varchar(100) = null,
@StartDate datetime = null,
@EndDate datetime = null,
@StartTime smallint = null,
@EndTime smallint = null,
@House_No varchar(8) = null,
@Premiere char(1) = null,
@Liveflag char(1) = null,
@Sddflag char(1) = null
AS
SET NOCOUNT ON
SELECT DISTINCT
dbo.Schedule_Reports.Report_Show_Date, dbo.Schedule_Reports.Report_Show_Time, dbo.Schedule_Reports.Report_Sort_Order,
dbo.Schedule_tester.tester_Show_End_Time, dbo.Schedule_tester.tester_Show_Name, dbo.Schedule_tester.tester_House_No,
dbo.Vw_Genre_Catgry_Shows.Category_Code, dbo.Vw_Genre_Catgry_Shows.Category_Desc, dbo.Vw_Genre_Catgry_Shows.Genre_Code,
dbo.Vw_Genre_Catgry_Shows.Genre_Desc, DATENAME(Weekday, dbo.Schedule_Reports.Report_Show_Date) AS WD,
dbo.Schedule_tester.tester_Episode_No, dbo.Schedule_tester.tester_Premiere_Flag, dbo.Schedule_tester.tester_Live_SDD_Flag,
dbo.Episodes.Episode_Title AS tester_Episode_Title, dbo.Episodes.Episode_Desc AS tester_Episode_Desc
FROM dbo.Episodes RIGHT OUTER JOIN
dbo.Schedule_tester ON dbo.Episodes.Episode_No = dbo.Schedule_tester.tester_Episode_No AND dbo.Episodes.Episode_Title = dbo.Schedule_tester.tester_Episode_Title RIGHT OUTER JOIN
dbo.Schedule_Reports ON dbo.Schedule_tester.tester_Show_Date = dbo.Schedule_Reports.Report_Show_Date AND
dbo.Schedule_tester.tester_Show_Start_Time = dbo.Schedule_Reports.Report_Show_Time LEFT OUTER JOIN
dbo.Vw_Genre_Catgry_Shows ON dbo.Schedule_tester.tester_Show_Name = dbo.Vw_Genre_Catgry_Shows.Show_Name
WHERE dbo.Schedule_Reports.Report_Show_Date >= COALESCE(@StartDate, dbo.Schedule_Reports.Report_Show_Date )
and dbo.Schedule_Reports.Report_Show_Date <= COALESCE(@EndDate, dbo.Schedule_Reports.Report_Show_Date )
and dbo.Schedule_Reports.Report_Sort_order >= COALESCE(@StartTime, dbo.Schedule_Reports.Report_Sort_order)
and dbo.Schedule_Reports.Report_Sort_order <= COALESCE(@EndTime, dbo.Schedule_Reports.Report_Sort_order)
and ( dbo.Episodes.Episode_Desc like COALESCE(@Searchtext, dbo.Episodes.Episode_Desc)
OR dbo.Episodes.Episode_Title like COALESCE(@Searchtext, dbo.Episodes.Episode_Title)
OR dbo.Schedule_tester.tester_Show_Name like COALESCE(@Searchtext, dbo.Schedule_tester.tester_Show_Name ))
and dbo.Schedule_tester.tester_House_No = COALESCE(@House_No, dbo.Schedule_tester.tester_House_No)
and dbo.Schedule_tester.tester_Premiere_Flag = COALESCE(@Premiere, dbo.Schedule_tester.tester_Premiere_Flag)
and (dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Liveflag, dbo.Schedule_tester.tester_Live_SDD_Flag) OR
dbo.Schedule_tester.tester_Live_SDD_Flag = COALESCE(@Sddflag, dbo.Schedule_tester.tester_Live_SDD_Flag))
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-02 : 11:56:20

You can use osql or (I prefer) bcp with a queryout.
I would crate the file format in the sp then call it from another sp using bcp.

your sp above would become
select coalesce(convert(varchar(100),fld),'')
+ ',' + coalesce(convert(varchar(100),fld),'')
+ ',' + coalesce(convert(varchar(100),fld),'')
+ ',' + coalesce(convert(varchar(100),fld),'')
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources