Batch File Menu

[aveit]

Hi

I am creating a batch file used to run a number of SQL’s. Is it possible to have a batch file menu that will populate on the contents of a folder?

e.g. I have a folder C:\test containing:

test1.sql
test2.sql
test3.sql

I would like a menu which would ask me to select one of these files. Based on my selection I would then want to run the following with FILENAME replaced with the above selection:

sqlcmd -Stestdb -dtest -E -iC:\Test\FILENAME -oC:\output\FILENAME -s" "

Is this possible ?!!

Many thanks
Paul

[dbenham]

I assume you want your output and input files to have different extensions, and that your input always has a .sql extension. I chose .out for the output.

Try this (untested):

Code: Select all

@echo off
setlocal enableDelayedExpansion

::First clear any existing options
for /f "delims==" %%V in ('set option') do set "%%V="

::Define the options and display the Menu
set id=0
echo(
echo(SQL Script Menu:
for %%F in ("c:\test\*.sql") do (
  set /a id+=1
  set "option!id!=%%~nF"
  echo(  !id! - %%~nF
)

::Get the user's choice
set "choice="
set /p "choice=Which script do you want to run? "

::Execute the script only if the entered option is valid
if defined option%choice% sqlcmd -Stestdb -dtest -E -i"C:\Test\!option%choice%!.sql" -o"C:\output\!option%choice%!.out" -s" "

Dave Benham

[Rileyh]

@dbenham,
This post is not in relation to the topic, but what do you mean by:

Code: Select all

if defined (whatever)

Rileyh

[dbenham]

@Rileyh

The HELP documentation is fairly clear for the IF statement.

Excerpted directly from HELP IF:

Code: Select all

...
...
    IF DEFINED variable command
...
...
The DEFINED conditional works just like EXIST except it takes an
environment variable name and returns true if the environment variable
is defined.
...
...

You should be able to understand the above explanation. If it is still unclear, try some simple experiments to see if you can figure it out.

Dave Benham

[Rileyh]

@dbenham,
Thanks for the post- it makes sense. I must have missed it on the cmd help line.

Thanks,
Rileyh

[aveit]

Hi Dave

This works a treat – many thanks for this.  Just a quick question – would it be possible to add an option after the selection from the folder?

e.g.

test1.sql (from test folder)
test2.sql (from test folder)
test3.sql (from test folder)

Press Q to quit (go to :QUIT in batch file)
Press M to go to Main Menu (go to :MainMenu in batch file)

I have tried to do this but have not yet been able to.

Many thanks
Paul

[dbenham]

I had fun creating a general purpose menu function to handle your request. I've included a sample script that shows how easy it is to use. There are lots of possibilities to tweak the behavior, but this should give you a good framework to build upon.

Documentation is included in the code, especially for the critical :execMenu function.

Code: Select all

@echo off
setlocal
set prompt="Enter an option: "

:mainMenu
:: I used line continuation (^) to format the definition so it is easy to read
:: I also added extra spaces in front of the Command for readability.
:: Do not add space before or after the Option or before the Description.
  set menu="1;Main Task 1;        call :main1"^
           "2;Main Task 2;        call :main2"^
           "3;Main Task 3;        call :main3"^
           "A;Admin Menu;         goto :adminMenu"^
           "S;Execute SQL Script; goto :sqlMenu"^
           "Q;Quit;               goto :quit"
  call :execMenu "Main Menu" menu %prompt% action
  %action%
  pause
goto :mainMenu

:main1
  echo Perform Main Task 1
exit /b

:main2
  echo Perform Main Task 2
exit /b

:main3
  echo Perform Main Task 3
exit /b

:adminMenu
  set menu="1;Admin Task 1; call :admin1"^
           "2;Admin Task 2; call :admin2"^
           "M;Main Menu;    goto :mainMenu"^
           "Q;Quit;         goto :quit"
  call :execMenu "Admin Menu" menu %prompt% action
  %action%
  pause
goto :adminMenu

:admin1
  echo Perform Admin Task 1
exit /b

:admin2
  echo Perform Admin Task 2
exit /b

:sqlMenu
  :: Here is a menu definition without the nice formatting
  set menu="M;Main Menu;goto :mainMenu" "Q;Quit;goto :quit"
  call :execMenu "SQL Script Menu" menu %prompt% action script "c:\test\*.sql"
  ::
  :: I am relying on the fact that action will either be empty so nothing happpens
  :: and it falls through to script processing, or else it contains a GOTO so that
  :: nothing after action will take place
  %action%
  for %%F in ("%script%") do echo sqlcmd -Stestdb -dtest -E -i"%script%" -o"C:\output\%%~nF.out" -s" "
  pause
goto :sqlMenu

:execMenu "MenuHeader" menuVar "Prompt" rtnActionVar [rtnScriptVar "scriptPath" ...]
::
:: Displays a menu based on input and returns a validated response.
:: The menu always contains a static list of options at the bottom.
:: The menu may also consist of a dynamic list of script options
:: at the top derived from up to 4 file path specifications.
::
::  "MenuHeader" = A title that is displayed at the top of the menu.
::
::  menuVar = The name of a variable containing a space delimited list
::     of quoted options. Each option should have the format of
::
::        "Option;Description;Command"
::
::     where
::
::        Option = The option the user will enter.
::
::        Description = Description of the option.
::
::        Command = The code that is to be executed for the option.
::
::  "Prompt" = The prompt to be displayed at the bottom of the menu.
::
::  rtnActionVar = The name of a variable that is to receive the code
::     to be executed for the entered option.
::
::  rtnScriptVar = The optional name of a variable that is to receive the
::     full path of the selected script.
::
::  "scriptPathN" = The optional paths from which the list of scripts is
::     derived. Each path should include at least one wildcard (* or ?) in
::     the name or extension portion of the path. Up to 4 paths may be
::     specified.
::
  setlocal enableDelayedExpansion
  :execMenuRetry
  if "%~1" neq "" (
    cls
    echo(%~1
  )
  for /f "delims==" %%O in ('set fileOption 2^>nul') do set "%%O="
  for /f "delims==" %%O in ('set menuOption 2^>nul') do set "%%O="
  if "%~5" neq "" (
    set id=0
    for %%F in (%6 %7 %8 %9) do (
      set /a id+=1
      set "fileOption!id!=%%~fF"
      echo(  !id! - %%~nxF
    )
  )
  for %%O in (!%~2!) do for /f "tokens=1-3 delims=;" %%A in (%%O) do (
    set menuOption%%A=%%C
    echo(  %%A - %%B
  )
  echo(
  set "menuChoice="
  set /p "menuChoice=%~3"
  echo(
  set "chosenAction="
  set "chosenScript="
  set "chosenAction=!menuOption%menuChoice%!"
  set "chosenScript=!fileOption%menuChoice%!"
  if not defined chosenAction if not defined chosenScript (
    echo(Error: %menuChoice% is not a valid option
    pause
    goto :execMenuRetry
  )
  (endlocal
    set "%~4=%chosenAction%"
    if "%~5" neq "" set "%~5=%chosenScript%"
  )
exit /b

:quit
  echo You opted to quit
  pause
exit /b

Dave Benham