SQL via Batch - Not working well

Question

I have written a batch script to get a particular value or update values from sql database. It works only for some times,listed below.

case 1:

• First time not works (not update) but returns - 1 rows affected

• Second time it works (update well in db) returns - 1 rows affected

case 2:

some times thrown an error (Sqlcmd: 'username=': Invalid argument. Enter '-?' for help.)

Find my cmd error here.

Please find my script and sql code below.

main.bat

@ECHO OFF

:: DB Properties
SET dbPath=XXXX 
SET dbUsername=YYYY
SET dbPassword=ZZZZ
SET dataBaseName=KKKK

:: SQL Properties
SET originId=50

ECHO DB NAME -  %dataBaseName%

ECHO Select an option
ECHO 1.Token
ECHO 2.Activate

SET /P option=Enter an option:

IF "%option%"=="1" ( SET /P inputUserName=Enter username: 
    sqlcmd -S %dbPath% -U %dbUsername% -P %dbPassword% -i token.sql -v dbName=%dataBaseName% username=%inputUserName% )

IF "%option%"=="2" ( SET /P inputUserName=Enter username: 
    sqlcmd -S %dbPath% -U %dbUsername% -P %dbPassword% -i update.sql -v dbName=%dataBaseName% username=%inputUserName% orgId=%originId% )

ECHO Executed Successfully ..!!

::PAUSE

token.sql

use $(dbName);

select VerificationCode from users where username = '$(username)'

update.sql

use $(dbName);

UPDATE users
SET passwordHash = (SELECT PasswordHash FROM users WHERE userid = $(orgId)),
    passwordsalt = (SELECT passwordsalt FROM users WHERE userid = $(orgId)),
    isActive = 1,
    emailConfirmed = 1
WHERE username = '$(username)'

How to resolve this problem ?

Answer 1

Reference here

1. Enable the delayed expansion

setlocal enabledelayedexpansion

2. Change the passed reference from %inputUserName% to !inputUserName!

It works !!