How can I add a variable to a batch file?

Question

Hi all I am new to batch files and struggling.I am using sql server 2008

I have created a single batch file that execute other batch files + sql scripts. I would like to use variables how can I do that? I would like to set the path as a variable. I could have more than one path depending on the script's location.

:On Error exit 

 CALL C:\mypath\Scripts\ExecSqlScripts.bat
 CALL C:\mypath\Scripts\ExecSqlScriptsTwo.bat

 SQLCMD -S (Local) -i C:\mypath\Scripts\InsertUsernameTwo.sql
 SQLCMD -S (Local) -i C:\mypath\Scripts\InsertUsernameThree.sql
 SQLCMD -S (Local) -i C:\mypath\Scripts\InsertUsernameFour.sql

Any Suggestions?

Answer 1

You need the set command - http://www.computerhope.com/sethlp.htm

:On Error exit 

set thePath=C:\mypath\Scripts

 CALL %thePath%\ExecSqlScripts.bat
 CALL %thePath%\ExecSqlScriptsTwo.bat

 SQLCMD -S (Local) -i %thePath%\InsertUsernameTwo.sql
 SQLCMD -S (Local) -i %thePath%\InsertUsernameThree.sql
 SQLCMD -S (Local) -i %thePath%\InsertUsernameFour.sql

The set thePath=C:\mypath\Scripts can be called from outside the batch file too.

Another solution to avoiding repetitive code to use the for command.

for %%f in (ExecSqlScripts ExecSqlScriptsTwo) do call %%f.bat

This does the equivalent of the CALL %thePath%\ExecSqlScripts.bat CALL %thePath%\ExecSqlScriptsTwo.bat

for %%f in (InsertUsernameTwo InsertUsernameThree InsertUsernameFour) do call SQLCMD -S (Local) -i %thePath%\%%f.sql

This does the equivalent of the

 SQLCMD -S (Local) -i %thePath%\InsertUsernameTwo.sql
 SQLCMD -S (Local) -i %thePath%\InsertUsernameThree.sql
 SQLCMD -S (Local) -i %thePath%\InsertUsernameFour.sql