SQLCMD: Prompt for Variable?

Question

Coming from an Oracle background, Oracle's SQLPlus would let you indicate a variable. If the variable wasn't set, you'd be prompted to provide a value.

I'm using SQLCMD, using the $([var_name]) syntax. In SSMS SQLCMD mode, I get:

A fatal scripting error occurred.
Variable tbl_name is not defined.

...for trying to run:

SELECT COUNT(*) FROM $(tbl_name)

Does SQLCMD provide the same functionality as SQLPlus? If so, what am I doing wrong?

@Tom H.: I'm only testing it via the SQLCMD mode ATM. My point is, I've seen how to supply the variables, but want to know if SQLCMD supports user interaction like SQLPlus does. — OMG Ponies

Florian Reischl Florian Reischl · Accepted Answer · 2010-07-21T18:59:51

SQLCMD does not support prompting for missing variable names. However, you can use SSMS in SQLCMD mode. Dunno how you're error was caused, but this works for me:

:setvar table "sys.tables"

SELECT * FROM $(table);

SQLCMD: Prompt for Variable?

2 Answers