using a While Loop in batch file with sql

Question

I am new to Batch.

My code:

    @ECHO OFF

    SET colu=
    SET sn=
    SET /P colu= Enter column name:
    SET /P sn= Enter ID : 

     sqlcmd -U USER -P PWORD -S SERVER -d DBNAME-i sqlSCRIPT.sql -o LOG.txt -v delete=%colu% d_id=%%I

    pause

 SET /P sn= Enter ID :
     sqlcmd -U USER -P PWORD -S SERVER -d DBNAME-i sqlSCRIPT.sql -o LOG.txt -v delete=%colu% d_id=%%I

pause

 SET /P sn= Enter ID :
     sqlcmd -U USER -P PWORD -S SERVER -d DBNAME-i sqlSCRIPT.sql -o LOG.txt -v delete=%colu% d_id=%%I

The code works and does what I need it to do which is delete records from the db but I was hoping that there was a more efficient way to write it.

How would I implement a while loop in batch that will keep asking the user to enter the id until they press the end key? Also I realize that having pause in between sqlcmd is highly inconvenient.

Jason Faulkner · Accepted Answer · 2015-10-13 13:17:58Z

2

How about something like this:

@ECHO OFF

SETLOCAL

SET /P "colu=Enter column name:"

:Prompt
REM Clear any existing values.
SET "sn="

ECHO Enter a blank value to stop the operation.
SET /P "sn=Enter ID:"

REM Check for exist conditions.
IF "%colu%"=="" GOTO :EOF
IF "%sn%"=="" GOTO :EOF

REM If we get here, data was entered for both
sqlcmd -U USER -P PWORD -S SERVER -d DBNAME-i sqlSCRIPT.sql -o LOG.txt -v delete=%colu% d_id=%sn%
ECHO.
ECHO.

REM Ask again.
GOTO Prompt

:EOF
ENDLOCAL

This will keep prompting until you enter a blank value for either colu or sn. Also, I noticed you are using %%I in your SQL statement - I think you meant to use %sn% instead.

answered Oct 13, 2015 at 13:17

Jason Faulkner

6,6682 gold badges31 silver badges33 bronze badges

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

1 Comment

royalblue Over a year ago

Thanks!! This was very helpful.

Collectives™ on Stack Overflow

using a While Loop in batch file with sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related