Rewriting script to use a loop with incrementing values

I have this batch (CMD) file to update my database using incremental sql update scripts.

The problem I'm having is that I need to add a new "section" for each update. Can
this be rewritten to use a loop and increment the variables curmin or curmaj each
time it needs to run?
Variables tgtmaj and curmaj can only be values 202, 300, or 301.

if curmaj = 202 and tgtmaj > 202, then curmaj needs to increment to 300 when
curmin = 43.
if curmaj = 202 and tgtmaj > 300, then curmaj needs to increment to 301 when
curmin = 54.

@echo off
REM: Usage: UpgradeDatabase Server Database [DBVerNum] [DBUpdateNum]

if '%1' == '' goto usage
if '%2' == '' goto usage

REM:**************************************************************************
REM: These two values (tgtmaj/tgtmin) have to be changed for each update
REM: They are set the the last update available.
REM:**************************************************************************
if '%3' == '' (set tgtmaj=301) else (set tgtmaj=%3)  
if '%4' == '' (set tgtmin=60) else (set tgtmin=%4)

if '%5' NEQ '' goto usage

if '%1' == '/?' goto usage
if '%1' == '-?' goto usage
if '%1' == '?' goto usage
if '%1' == '/help' goto usage

REM:**************************************************************************
REM: Get the values currently in the DB. The DB_VERSION_NUM HAS to be
REM: multiplied by 100 so it can be returned as an integer in the
REM: environment variable %ERRORLEVEL%. In the DB, it has a value of 2.02 or 3.0 or 3.01
REM:**************************************************************************
osql -S %1 -d %2 -E -b -r0 -n -o NUL -Q "EXIT( SELECT (DB_VERSION_NUM * 100) FROM SYCFSYSTEM )"
set curmaj=%ERRORLEVEL%
osql -S %1 -d %2 -E -b -r0 -n -o NUL -Q "EXIT( SELECT (DB_UPDATE_NUM) FROM SYCFSYSTEM )"
set curmin=%ERRORLEVEL%
goto update-%curmaj%.%curmin%.test

:update-301.42.test
set curmaj=301
set curmin=43
if %tgtmaj% LSS %curmaj% goto finish
if %tgtmaj% GEQ %curmaj% goto update-%curmaj%.%curmin%.exec
if %tgtmin% LSS %curmin% goto finish
if %tgtmin% GEQ %curmin% goto update-%curmaj%.%curmin%.exec
REM:**************************************************************************
REM: Now, run the script for 301.42, incrment the curmin value  and skip to the next update
REM:**************************************************************************
:update-301.42.exec
echo Running DB Update script update-%curmaj%-%curmin%.sql
osql -S %1 -d %2 -E -b -r0 -n -i "update-%curmaj%-%curmin%.sql" >>%2-%curmaj%-%curmin%.log
set curmaj=301
set curmin=43
if %tgtmaj% LSS %curmaj% goto finish
if %tgtmaj% GEQ %curmaj% goto update-%curmaj%.%curmin%.exec
if %tgtmin% LSS %curmin% goto finish
if %tgtmin% GEQ %curmin% goto update-%curmaj%.%curmin%.exec
if %ERRORLEVEL% NEQ 0 goto errors

:update-301.43.test
set curmaj=301
set curmin=44
if %tgtmaj% LSS %curmaj% goto finish
if %tgtmaj% GEQ %curmaj% goto update-%curmaj%.%curmin%.exec
if %tgtmin% LSS %curmin% goto finish
if %tgtmin% GEQ %curmin% goto update-%curmaj%.%curmin%.exec
REM:**************************************************************************
REM: Now, run the script for 301.43, incrment the curmin value  and skip to the next update
REM:**************************************************************************
:update-301.43.exec
echo Running DB Update script update-%curmaj%-%curmin%.sql
osql -S %1 -d %2 -E -b -r0 -n -i "update-%curmaj%-%curmin%.sql" >>%2-%curmaj%-%curmin%.log
set curmaj=301
set curmin=44
if %tgtmaj% LSS %curmaj% goto finish
if %tgtmaj% GEQ %curmaj% goto update-%curmaj%.%curmin%.exec
if %tgtmin% LSS %curmin% goto finish
if %tgtmin% GEQ %curmin% goto update-%curmaj%.%curmin%.exec
if %ERRORLEVEL% NEQ 0 goto errors

REM:**************************************************************************
REM: Note that this last update is different than the others
REM: MAKE SURE you make the last update is designed like this one.
REM:**************************************************************************

:update-301.44.test
set curmaj=301
set curmin=45
if %tgtmaj% LSS %curmaj% goto finish
if %tgtmaj% GEQ %curmaj% goto update-%curmaj%.%curmin%.exec
if %tgtmin% LSS %curmin% goto finish
if %tgtmin% GEQ %curmin% goto update-%curmaj%.%curmin%.exec
REM:**************************************************************************
REM: Now, run the script for 301.44. This is the last update so we don't need
REM: increment curmin and to skip to the next update
REM:**************************************************************************
:update-301.44.exec
echo Running DB Update script update-%curmaj%-%curmin%.sql
osql -S %1 -d %2 -E -b -r0 -n -i "update-%curmaj%-%curmin%.sql" >>%2-%curmaj%-%curmin%.log
if %ERRORLEVEL% NEQ 0 goto errors

:update-301.999.test

goto finish

REM: How to use screen
:usage
echo.
echo Usage: UpgradeDatabase Server Database [DBVerNum] [DBUpdateNum]
echo.
echo   Server:      the name of the target SQL Server
echo   Database:    the name of the target Database
echo   DBVerNum:    the target DB Version Number
echo   DBUpdateNum: the target DB Update  Number
echo.
echo If no release information is provided, the database is upgraded to
echo the current release.
echo.
echo Output logs are written by appending the release numbers to the database
echo name (i.e., [Database]-[DBVerNum]-[DBUpdateNum].log).
echo.
echo Example: UpgradeDatabase.cmd (local) Storeworks 301 44
echo.
echo.
goto done

REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
type %2*.log
goto done

REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
@echo on
LVL 27
Eddie ShipmanAll-around developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveGTRCommented:
I don't understand your version requirements, but you can save the last version information to a file and reread it in at the beginning of the processing:

@echo off

setlocal

REM ** You can set your version file to any name you want
set versionFile=version.txt

REM ** Set default values
set /a curmaj=200
set /a curmin=0

for /f "tokens=1-2 delims=," %%a in ('type "%versionFile%" 2^>NUL') do set /a curmaj=%%a&set /a curmin=%%b

REM ** You can compare this against input information and increment it

set /a curmin+=1

if /i %curmin% GEQ 10 set /a curmaj+=100&set /a curmin=0

echo curmaj=%curmaj%
echo curmin=%curmin%

REM ** Do processing

(echo %curmaj%,%curmin%)>"%versionFile%"
Eddie ShipmanAll-around developerAuthor Commented:
That only goes through the loop one time, it does not go through 10 times like you think it should.
Eddie ShipmanAll-around developerAuthor Commented:
how does it know exactly what statements are IN the loop, anyway?
I mean how would the script know where the for actually ends,  i.e.

for ... do
begin
  // statements go here...
end
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

SteveGTRCommented:
So you want to do all of your processing that you originally posted 10 times? Incrementing a counter of some sort? To do this you could say:

@echo off

setlocal

REM ** Setup parameters into local variables

for /l %%a in (1, 1, 10) do call :PROCESS

goto :EOF

:PROCESS

REM ** Here's where your code would code

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eddie ShipmanAll-around developerAuthor Commented:
Can you explain the for loop parameters that you have for me, especially the (1,1,10)?

SteveGTRCommented:
The best explanation can be viewed by pulling up the for command's help, like so:

for /?

Here's what it says:

FOR /L %variable IN (start,step,end) DO command [command-parameters]

    The set is a sequence of numbers from start to end, by step amount.
    So (1,1,5) would generate the sequence 1 2 3 4 5 and (5,-1,1) would
    generate the sequence (5 4 3 2 1)

You can get help for any DOS command by typing command /? at the DOS prompt. For instance, if /?, setlocal /?, etc...
Eddie ShipmanAll-around developerAuthor Commented:
Here is my versioning scheme:

DBVersionNum = tgtmaj, curmaj
DBUpdateNum  = tgtmin, curmin

DBVersionNum = DBUpdateNum
--------------------------
202 = 35
202 = 36
202 = 37
202 = 38
202 = 39
202 = 40
202 = 41
202 = 42
300 = 43
300 = 44
300 = 45
300 = 46
300 = 47
300 = 48
300 = 49
300 = 50
300 = 51
300 = 52
301 = 53
301 = 54
301 = 55
301 = 56
301 = 57
301 = 58
301 = 59
301 = 60
301 = 61
Eddie ShipmanAll-around developerAuthor Commented:
I don't understand the %%a
SteveGTRCommented:
In my usage of for /l %%a is ignored, but if you wanted to use the enumerated number you'd say:

for /l %%a in (1, 1, 10) do echo %%a&call :PROCESS

This would output 1 through 10 and call the PROCESS subroutine 10 times.
SteveGTRCommented:
I'm not sure I understand your requirements. Do you just want to loop through your processing from 35 to 61? If so you could do something like this:

@echo off

setlocal

for /l %%a in (35, 1, 61) do call :PROCESS %%a

goto :EOF

:PROCESS

set tgtmin=%1
set curmin=%1

if /i %curmin% LSS 43 (
  set curmaj=202
) else (
    if /i %curmin% LSS 53 (
      set curmaj=300
    ) else (
        set curmaj=301
      )
    )
  )

set tgtmaj=%curmaj%

echo curmaj=%curmaj%, curmin=%curmin%
echo tgtmaj=%tgtmaj%, tgtmin=%tgtmin%

REM ** Do your stuff
Eddie ShipmanAll-around developerAuthor Commented:
Many thanks, I now have it working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft DOS

From novice to tech pro — start learning today.