Output Stored Procedure data to Text File

Here is the end of the Stored Procedure I have.  It has gone through many steps to create temporary values in the "Final Calculation".  After the "Final Calculation" is done, the results are outputted to the screen.

I want to send these values to a text or csv file.

Here is the code:

/* FINAL CALCULATION */
      SET @Denominator = (SELECT COUNT(VisitID) FROM #StrokePopulation)
      SET @Numerator = (SELECT COUNT(VisitID) FROM #Numerator)
      SET @Exclusions = (SELECT COUNT(VisitID) FROM #Exclusions)

/* Output for Attestation */
      SELECT
            @MeasureNumberIdentifier AS 'Measure'
         ,@ReportName AS 'Report Name'
         ,@Denominator AS 'Denominator'
         ,@Numerator AS 'Numerator'
         ,@Exclusions AS 'Exclusions'

/* Output Detail for patients meeting Performance Numerator */
IF @Detail = 'Y'
      SELECT 'Yes' AS [Meets Measure],*
      FROM #Numerator

Is "BCP" an option?

thanks

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
You can use BCP:

bcp "EXEC yourdtabase.dbo.spYourProcedure" queryout "C:\Temp\output_file.txt" -c -T

That command has to be executed on the same server where SQL server is. If you execute it from a different server you use:

bcp "EXEC yourdtabase.dbo.spYourProcedure" queryout "C:\Temp\output_file.txt" -c -Sserver -T

-T means trusted connection, if you are using SQL authentication:

bcp "EXEC yourdtabase.dbo.spYourProcedure" queryout "C:\Temp\output_file.txt" -c -Sserver -Uuser -Ppassword

As I remember there is an issue with temp table if it is created inside the procedure you execute and you select from it, might not work. Instead use a permanent table or select from the tables where your data is. A view will work:

bcp "select col1,col2,... from yourdatabase.dbo.vwYourView where <cond>" queryout "C:\Temp\output_file.txt" -c -T

or directly:

bcp yourdatabase.dbo.vwYourView out "C:\Temp\output_file.txt" -c -T

in which case entire content will be exported and you cannot select speciffic columns or use where clause. Note the change from queryout to out in the command. Could be also a table instead of a view.
0
DcpKingCommented:
If you're using SQL Server 2005 or later, you might consider putting it in an SSIS package and running that. Personally I find that a lot easier than using BCP. You can add a Dataflow task to the ControlFlow window, and an OLE DB source and a Flat File destination in the task. The source is your procedure and the dest. is your .csv file.

Hth   - Mike
0
GPSPOWAuthor Commented:
Do I need to qualify the "@" variables  ( @MeasureNumberIdentifier, @Numerator, etc) to send to the text file?

or

Do I need to store the output variables into a table and then use BCP to export the table to the text file?

Glen
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ZberteocCommented:
I you mean parameters then you would use them like when you execute normally any stored procedure. In the form with queryout option basically what bcp does is executing any SQL statement you give it in between quotes. So with parameters would be:

bcp "EXEC yourdtabase.dbo.spYourProcedure param1value, param2value..." queryout "C:\Temp\output_file.txt" -c -T

or

bcp "EXEC yourdtabase.dbo.spYourProcedure @param1=value1, @param2=value2..." queryout "C:\Temp\output_file.txt" -c -T
0
GPSPOWAuthor Commented:
Our server is down now.

As soon as the administrator reboots it, I will try out your suggestion.

Thanks

Glen
0
Mark WillsTopic AdvisorCommented:
You could also use BCP within the procedure...

create an additional param named @BCP INT = 0

then (by way of example) e.g.

-- these are obviously set somewhere else...
declare @bcp int = 0
declare @MeasureNumberIdentifier int = 0
declare @ReportName varchar(100)
declare @Denominator int = 0
declare @Numerator int = 0
declare @Exclusions int = 0

--set the bcp param
set @bcp = 1

-- now the code...

if @bcp = 0
BEGIN
   SELECT
          @MeasureNumberIdentifier AS 'Measure'
         ,@ReportName AS 'Report Name'
         ,@Denominator AS 'Denominator'
         ,@Numerator AS 'Numerator'
         ,@Exclusions AS 'Exclusions'
END
ELSE 
BEGIN
   declare @bcp_string varchar(800)
   set @bcp_string = 'bcp "select '+convert(varchar,isnull(@MeasureNumberIdentifier,'0'))+' AS [Measure], '''
                                       +isnull(@ReportName,'Report')+ ''' AS [Report Name], '
                                       +convert(varchar,isnull(@Denominator,'0'))+ ' AS [Denominator], '
                                       +convert(varchar,isnull(@Numerator,'0'))+ ' AS [Numerator], '
                                       +convert(varchar,isnull(@Exclusions,'0'))+ ' AS [Exclusions]" queryout "c:\ee\bout.txt" -Syour_named_instance -T -c -CACP'
   print @bcp_string

   exec master..xp_cmdshell @bcp_string
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
I like all the solutions.

Mark's was exactly what I was looking for.

Thanks

glen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.