bcp out and creating an empty file

[mipo]

I am using this bcp out construct and it works fine except that if the query does not return values it bcp's out a file anyhow. This is not wanted and I am looking for a work around.

SELECT @Year = CONVERT(varchar(4), @trxYearMonthStart, 120)
SELECT @Month = RIGHT(CONVERT(varchar(7), @trxYearMonthStart, 120),2)
SELECT @cmd = 'BCP "SELECT * FROM ' + @TableToBeCleaned + ''
SELECT @cmd = @cmd + ' WHERE '+ @SelectedColumn + ' BETWEEN '
print @cmd
SELECT @cmd = @cmd + '''' + CONVERT(varchar(10),@trxYearMonthStart,120) + ''' and ''' + CONVERT(varchar(10),@trxYearMonthEnd,120) + ''''
print @cmd
SELECT @cmd = @cmd + 'AND NOT EXISTS (Select * from DBCleanerHist Where TableName = ''' + @TableToBeCleaned + ''' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
print @cmd
SELECT @cmd = @cmd + ' " QUERYOUT ' + @DBCleanerBackUpPath+'\' +@TableToBeCleaned +'_'+ @Year + '_' + @Month + '.txt '
SELECT @cmd = @cmd + ' -c -C1250 -S -Uopms -Psmpo'
EXEC master.dbo.xp_cmdshell @cmd

The subquery checks first in DBCleanerHist if a file already has been extracted onto hd and if so do not create an empty file and overwrite an existing file.

thanks

mipo

[andi_g69]

Execute your subquery in advance and check whether you have to run your BCP command. If not just exit the procedure.