Using an Sql variable in a batch script

Question

I have looked at a number of threads covering this but am unable to apply the advice to my stituation. I want to be able to run a block of code only if file1 exists and the row count from a sql command return a count greater than 0. Both must be true to run the code block (:RUNCODE). I have set up a number of IF's to handle this which jump to either :RUNCODE or :END.

    SET SqlServerName="SERVER1"
    SET SqlDatabaseName="DB1"
    SET SQL="SELECT COUNT(*) FROM TABLE1" 

    SQLCMD.exe -S%SqlServerName% -E -d%SqlDatabaseName%  -Q%SQL% -h -1 -o RowCount.txt

    SET /p RowCount= <RowCount.txt
    DEL RowCount.txt

    echo %RowCount%


    IF EXIST "File1" IF %RowCount% GTR 0 
    (
            ECHO Running the position Loader for EFA...
            GOTO RUNCODE
     )


    IF NOT EXIST "File1" IF %RowCount% EQU 0
    (              
            ECHO The Position File Does not Exist...
            GOTO END
    )

    IF EXIST "File1" IF %RowCount% EQU 0
    (
            ECHO The File Does not Exist...
            GOTO END
    )

    IF NOT EXIST "File1" IF %RowCount% GTR 0
    (
            ECHO The Position File Does not Exist...
            GOTO END 
    )

    :RUNCODE 
    echo Running code block now now...
    EXIT

    :END
    echo conditions not met...
    EXIT

The code above gives me RowCount=0 (with white space in the variable) and appears to completely skip the if conditions.

Any help would be greatly appreciated.

Example of the ouput script. Note that there is white space in front of the 0 and not in front of (1 row affected)

      0
(1 row affected)

The file is just three lines. First has the row count (0) and has white space in front of the result. Then there is an empty line for line two. Finally '(1 row affected)' is on the third line. — ricky89
– ricky89, Commented Feb 28, 2014 at 14:26
You don't need to trim your RowCount variable. If you follow @MattWilliamson's answer and fix your (), your script should work fine. — Jon Senchyna
– Jon Senchyna, Commented Feb 28, 2014 at 15:05
@Jon, you were right. It still worked even with the white space. The brackets were the key issue — ricky89
– ricky89, Commented Mar 7, 2014 at 15:40

Matt Williamson · Accepted Answer · 2014-02-28 14:48:15Z

2

Your opening parenthesis for the IF's need to be on the same line.

IF EXIST "File1" IF %RowCount% GTR 0 (
        ECHO Running the position Loader for EFA...
        GOTO RUNCODE
)


IF NOT EXIST "File1" IF %RowCount% EQU 0 (              
        ECHO The Position File Does not Exist...
        GOTO END
)

IF EXIST "File1" IF %RowCount% EQU 0 (
        ECHO The File Does not Exist...
        GOTO END
)

IF NOT EXIST "File1" IF %RowCount% GTR 0 (
        ECHO The Position File Does not Exist...
        GOTO END 
)

Here is how you can get rowcount without the whitespace

SET SQL="SET NOCOUNT on SELECT COUNT(*) FROM dbo.ACCT" 

SQLCMD.exe -S%SqlServerName% -E -d%SqlDatabaseName%  -Q%SQL% -h -1 -o RowCount.txt
for /f "delims=" %%a in ('type Rowcount.txt') do Call :Trim rcount %%a
if /i exist RowCount.txt DEL /f /q RowCount.txt
echo %rcount%
exit /b

:Trim <return> <string>
for /f "tokens=1*" %%a in ("%*") do set "%%a=%%b"
exit /b

edited Feb 28, 2014 at 14:48

answered Feb 28, 2014 at 14:33

Matt Williamson

7,1691 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ricky89 Over a year ago

Hi Matt, could you give me a quick break down of what this code is doing? Its all a bit beyond me at the moment.

Matt Williamson Over a year ago

well, first I added set nocount on to the SQL statement so it doesn't print out (1 row affected) Then it uses a for loop to get the rowcount value out of the file and then the :trim function is called to remove the whitespace around the number. (the for loop does the magic)

Collectives™ on Stack Overflow

Using an Sql variable in a batch script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related