sql server - setting variables at runtime

Question

In Oracle you can use &&VAR_NAME in a script and then the script will ask you for that value when you run it.

In SQLSERVER you can use $(VAR_NAME) and reference a property file using:

    :r c:/TEMP/sqlserver.properties

And in the property file you have something like:

   :setvar VAR_NAME_some_value

Can you do the equivalent of &&VAR_NAME so the script asks you for the value when you run it instead of having the value predefined in a script.

Answer 1

There is nothing in sql server like this, you should predefine all parameters values before using them, like this:

DECLARE @i SMALLINT
SET @i = 1

Answer 2

If I've understood correctly, you're talking about variable substitution with the SQLCMD utility.

I don't see that SQLCMD supports the behaviour you describe.

An alternative would be to exploit the fact that SQLCMD will substitute the values of system or user environment variables (see the link above), and create a wrapper CMD script which prompts the user for the variable value(s) using SET with the /P flag.

Answer 3

The problem with having a form pop up and ask you for the parameter is that you normally want rather more control over the nature of the form, even for an admin script. I'd use the variable substitution in SQLCMD, from within a Powershell or Python script so you can provide the guy running the script a better and more helpful form. That would make a very powerful combination.

You can do quite a lot with template variable substitution in SSMS, but that would only go so far as formulating the correct SQL to execute. you'd then have to bang the button. It would be a bit clunky outside the development environment!