Scripting Variable To Environment Variable And Vice Versa

Can we pass values of scripting variables to environment variables when we are witing codes in sqlcmd scripts or batch scripts and vice versa ?

Solution 1:

Using batch variables in sqlcmd

a) Directly placing them in the command line. The cmd parser will expand the variables to the value as it is written in the line

set"lastName=Smith"
sqlcmd -Q "SELECT * From myTable Where LastName='%lastName%'"

b) Using declared variables in the sql query, and using the previous method to declare them in the sqlcmd command. If we have a query.sql filename containing

SELECT*From myTable Where LastName='$(LastName)'

Then it is possible to do the following call

set"lastName=Smith"
sqlcmd -i query.sql -v LastName="%lastName%"

Using output of sqlcmd in batch files

a) Send the output to a file (-o switch in sqlcmd) and then process it. See for /?

sqlcmd -i query.sql -o data.txt -h -1for /f "tokens=*" %%a in (data.txt) do ....

b) Directly process the output of the command. Again with for /f command.

for /f "tokens=*" %%a in ('sqlcmd -i query.sql -h -1') do ....

In both cases, if what is needed is to assign value to a batch variable, you will have something like

for /f "... options ..." %%a in ('sqlcmd -i query.sql -h -1') do (
    ....
    set"varname=%%a"
    ....
)

processing the output/file and assigning the data to the variables.