title: How to store output of a SQL Server stored procedure in a .txt file
body: <p>I have this stored procedure; I am printing the value of the variable in SSMS. 
  Instead, I want to store this result in a <code>.txt</code> file.</p>
  
  <p>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.</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Sharing the updated working SP here so that it might be useful to someone with a similar requirement  Thanks @David Browne - Microsoft</p>
  
  <pre><code>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, ' &gt; "', @fn, '"');
  
      print @cmd 
      exec xp_cmdshell @cmd,  no_output
  
      set @cmd  = concat('type "', @fn, '"');
  
      print @cmd 
      exec xp_cmdshell @cmd;
  
      END
      GO
  </code></pre>