How to store output of a SQL Server stored procedure in a .txt file

Question

I have this stored procedure; I am printing the value of the variable in SSMS. Instead, I want to store this result in a .txt file.

NOTE: I don't want to do it using SSMS options of right clicking on the result and then saving the result as. I want it to be done using any SQL code/built-in function directly in the stored procedure itself.

CREATE PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'

    PRINT 'Data is : ' + @var   
    /* SQL query here to store result of Print statement in text file */
END

EXEC [dbo].[usp_printresulttofile]

Sharing the updated working SP here so that it might be useful to someone with a similar requirement Thanks @David Browne - Microsoft

ALTER PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)

    DECLARE @fileExtension varchar(5) = 'txt'
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'
    PRINT 'Data is : ' + @var   


  declare @fn varchar(500) = 'c:/log/SP_output_'+@fileTimeStamp+'.'+@fileExtension;
    declare @cmd varchar(8000) = concat('echo ', @var, ' > "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd,  no_output

    set @cmd  = concat('type "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd;

    END
    GO

Answer 1

I might recommend creating a batch file to accomplish this. You can simply do this:

sqlcmd -i ExecSQLProc.sql > Results.txt

Save your EXEC command in a file named ExecSQLProc.sql and then create a batch script with the line above. You can also attach this to the scheduler if needed so that you can regularly generate these scripts. Also you can create a process to output whatever your process produces into an email using only SQL server and see the results this way as well.

Generally I have found it better to use the Operating System to manipulate individual files, if you want to append results you have options:

1. sqlcmd -i ExecSQLProc.sql >> CumulativeResults.txt
2. Update your stored process to keep track of your data in the database that is a large container - a blob for instance, and then when you execute your script it will generate a file that has everything in it.

Answer 2

Use bcp command to copy data into any format. You just need to mention the format in which you want. Like .text

bcp 'select * from table' queryout c:\sql\bcp.txt -c -T

Example and explaination in below link :- https://www.mssqltips.com/sqlservertip/4353/export-sql-server-records-into-individual-text-files/