Combine command line and T-SQL - SQL Server

Question

I am trying to execute some sqlcmd through T-SQL on SQL Server 2008. There is a part of my code where I am checking a data file size and if that data file size does not equal to 0, then start deleting the specific table so I can BCP in new data.

Below is my code that is not being executed:

SET @myVariable = '
SETLOCAL 
FOR %%R IN (X:\Main Folder\Data\'+@databaseName+'_'+@tableName+'.dat) DO SET size=%%~zR 
IF %size% NEQ 0 (
        SQLCMD -E -S my-server-name -Q "DELETE FROM '+@databaseName+'.'+@schemaName+'.'+@tableName+';" >> X:\Main Folder\Log\Log.txt 
)'

EXEC master..xp_cmdshell @myVariable

For some reason when I execute my stored procedure, the code above seems to be skipped because it does not shoot back any error messages.

EDIT: After re-adjusting the spacing and my code, @myVariable, gets executed now. However, it still does not work in regards that it still deletes the table even though the data file size = 0. However, when I hard code it within a batch file, it works perfectly fine. Any ideas?

Answer 1

You need to use single % in your for loop as you are not executing the code in a batch file (that requires %%), see this post for some further clarification. So your for loop should be:

FOR %R IN (X:\Main Folder\Data\'+@databaseName+'_'+@tableName+'.dat) DO SET size=%~zR