get .BAT file or SQLCMD to prompt user for input

I am attempting to run the following query

sqlcmd -STEMP7 -E -devolive_base -w256
    -QEXIT("DECLARE @开发者_如何学编程r int EXEC @r = usp_AddToObservationtbl $,$,$ SELECT @r")

I am attempting to get the BAT file to ask the user for 3 variables.... can anyone help me ?

You can use set /p to get variables:

set /p var1=Enter variable 1
set /p var2=Enter variable 2
set /p var3=Enter variable 3

...then use them in your command line:

sqlcmd -STEMP7 -E -devolive_base -w256 -QEXIT("DECLARE @r int EXEC @r = usp_AddToObservationtbl %var1%,%var2%,%var3% SELECT @r")

Bear in mind this still leaves you open to SQL Injection attacks or someone entering something destructive.

Thanks the comments people.

The problem appeared to be my syntax for SQLCMD is incorrect, but its not throwing an error, the variable substitution does work if I replace it with osql instead. So this works fine

set /p var1= Enter Userid
set /p var2= Enter Casino Name  
set /p var3= Enter Screename 

osql -STEMP7 -E -devolive_base -w256 -QEXIT("DECLARE @r int EXEC @r = usp_AddToObservationtbl %var1%,%var2%,%var3% SELECT @r") -b -oc:\bat\observation.log
exit errorlevel

Here is a simple example with the basic problem solved and specifics of your actual query removed.

I have removed a command to change the Windows prompt and a command to turn echo off, as these were just used to beautify the output, not part of the solution. I've also used an underscore in the data input prompt where you would normally have a space, so that you can see it. I have left a pause at the end of the bat file, which is only necessary to see the output, in case you want to try it out yourself.

test.bat

set /p batchvariable=Enter a number:_
sqlcmd -v sqlvariable=%batchvariable% -i test.sql
pause

test.sql

SELECT $(sqlvariable) AS [result];

When you run the batch file, this is what you get.

In practice you are likely to use the same names for the batch variable and the SQL variable. They are different above to illustrate what's what.

If you need to do this, you may need more than one variable. You handle this by repeating the -v sqlvariablename=%batchvariablename% part of the sqlcmd, e.g.

-v v1=%v1% -v v2=%v2%

test.bat

set /p item=Item : 
set /p cost=Cost : 
sqlcmd -v item="%item%" -v cost=%cost% -i test.sql

test.sql

SELECT '$(item)' AS [item], $(cost) AS [cost];

Note that there are quotes added into this second example (for string data) which didn't appear in the first example (with numerical data only).

You might want to run the example with Box of Chocolates for the item field and check the behavior.