SQLCMD with 1 return value

Question

I am trying to return a single value from a sqlcmd query used in a bat file. It seems that i have some errors with the syntax used in my sqlcmd query. I am very new to coding in cmd, any guidance would help. I am using this query on a server.

@ECHO off

echo _________________________________________________________

Echo The following inputs are required:
Echo    [1] Database Server Name
Echo    [2] Configuration Type

echo ---------------------------------------------------------
CD %~dp0

SET /p DBSERVER="Enter [1]: "
SET /p CFIGTYPE="Enter [2]: "


echo Deploying...
ECHO %CFIGTYPE%

::-----------------------------------------------TEST1----------------------------------------
IF (%CFIGTYPE% == 'TEST1' ){

for /f %%a in ('sqlcmd -S %DBServer% -v DBServer="%DBServer%" -Q "SET NOCOUNT ON; select ConfigValue FROM SERVICE.dbo.configuration WHERE ConfigOption = 'TEST1' "') 
    do set ColumnVar=%%a
echo %ColumnVar%
CMD \K  
    IF (%ColumnVar% == 'Y'){
        Echo WOHOOOO
        cmd /k
    }
    Else{
        ECHO NOOOOOO
        CMD /K
    }

My code just closes after it reaches the sqlcmd portion.

---

Revised code in light of comments:

(does not solve problem)

@ECHO off

echo _________________________________________________________

Echo The following inputs are required:
Echo    [1] Database Server Name
Echo    [2] Configuration Type

echo ---------------------------------------------------------

SET /p DBSERVER="Enter [1]: "
SET /p CFIGTYPE="Enter [2]: "


echo Deploying...
ECHO %CFIGTYPE%

::-----------------------------------------------TEST1----------------------------------------
IF "%CFIGTYPE%"=="TEST1" (

for /f %%a in ('sqlcmd -S %DBServer% -v DBServer="%DBServer%" -Q "SET NOCOUNT ON; select ConfigValue FROM SERVICE.dbo.configuration WHERE ConfigOption = 'TEST1' "') 
    do set ColumnVar=%%a
echo %ColumnVar%
    IF "%ColumnVar%"=="Y" (
        Echo WOHOOOO
        cmd /k
    )
    Else (
        ECHO NOOOOOO
        CMD /K
    )
)

---

New revision:

@ECHO off

echo _________________________________________________________

Echo The following inputs are required:
Echo    [1] Database Server Name
Echo    [2] Configuration Type

echo ---------------------------------------------------------

SET /p DBSERVER="Enter [1]: "
SET /p CFIGTYPE="Enter [2]: "


echo Deploying...
ECHO %CFIGTYPE%

::-----------------------------------------------TEST1----------------------------------------
IF "%CFIGTYPE%"=="TEST1" (

for /f %%a in ('sqlcmd -S %DBServer% -v DBServer="%DBServer%" -Q "SET NOCOUNT ON; select ConfigValue FROM SERVICE.dbo.configuration WHERE ConfigOption = 'TEST1' "')
    IF "%%a"=="Y" (
        Echo WOHOOOO
        PAUSE
    ) Else (
        ECHO NOOOOOO
        PAUSE
    )
)

Answer 1

I didnt manage to find a solution that allows me to set a value to a variable from a sql query. Instead, i have output the results to a file and read the results from there and then delete the file. Here goes the code:

@ECHO off    

echo _________________________________________________________

Echo The following inputs are required:
Echo    [1] Myserver


echo ---------------------------------------------------------
CD %~dp0

SET /p DBSERVER="Enter [1]: "


:: Get Configuration Type
sqlcmd -S %DBServer% -E  -i test.sql -b -v TYPE="CFIGTYPE" -h -1 >result.txt
set /p CFIGTYPE=<result.txt
call :Trimspaces CFIGTYPE %CFIGTYPE%
del "result.txt"


:: ------------------------------------------------ FUNCTIONS--------------------------------------------------------------------------------------------------------------
:trimSpaces2 retval string -- trims spaces around string and assigns result to variable
::                         -- retvar [out] variable name to store the result in
::                         -- string [in]  string to trim, must not be in quotes
for /f "tokens=1*" %%A in ("%*") do set "%%A=%%B"
EXIT /b

:trimSpaces varref -- trims spaces around string variable
::                 -- varref [in,out] - variable to be trimmed
call call:trimSpaces2 %~1 %%%~1%%
EXIT /b

Since the output file would contain empty i have included a function taken from https://www.dostips.com/DtTipsStringOperations.php

That would eliminate the empty spaces when reading the file.

I have an extra test.sql which contains the following code:

SET NOCOUNT ON;
select configvalue FROM Service.dbo.Configuration WHERE ConfigOption = '$(type)'