Stored Procedure results into a file only if not null

Question

I am trying to execute Stored Procedure on a regular basis using a SQL Job and store the results of this into a .txt file in a folder location.

I have used the BCP command which is like:

DECLARE @command VARCHAR(1000)
SET @command = 'BCP "Exec [DatabaseName].[dbo].[StoredProcedureName] " queryout "D:\In\ErrorDetails'+ '.txt" -c -T -t -k'
EXEC xp_cmdshell @command

I need this to execute only if results of SP are not null.

Answer 1

You can use temp table to store data generated by SP, if there are any rows - write it to file

USE tempdb

IF OBJECT_ID(N'#MyTempTable') IS NOT NULL
BEGIN
    DROP TABLE #MyTempTable
END

SELECT * INTO #MyTempTable 
FROM OPENROWSET('SQLNCLI', 'Server=HOME\SQLEXPRESS;Trusted_Connection=yes;',
     'EXEC Test.dbo.StoredProcedureName');

IF (SELECT COUNT(*) FROM #MyTempTable) > 0
BEGIN
    DECLARE @command VARCHAR(1000) 
    SET @command = 'BCP "USE tempdb SELECT * FROM #MyTempTable " queryout "D:\In\ErrorDetails'+ '.txt" -c -T -t -k' 
    EXEC xp_cmdshell @command
END