sqlcmd: Add new Command to Assign Variable Default Values by JediSQL

Closed

as Won't Fix Help for as Won't Fix

Type:	Suggestion
ID:	776539
Opened:	1/14/2013 3:38:47 PM
Access Restriction:	Public

Workaround(s)

view

I would like to create sqlcmd scripts that:
1) have $(variables) so I can control behavior at runtime, and
2) can also work without any specifying any variable values by -v or environment variables.

The :setvar command can ensure that value-assigned variables exist, but there is no ability to assign runtime values because the :setvar command has the highest precedent.

It would be nice if there was a new sqlcmd command, e.g., :defaultvar, that had a precedence lower than "System-level environmental variables." This way a script would always have its variables defined, AND they could also always be overridden at run time.

This also has the benefit of not altering the behavior of the :setvar command as proposed in this connect thread:
"In Sqlcmd scripts setvar should have a lower precedence than command line variable assignment" (ID = 382007)
https://connect.microsoft.com/SQLServer/feedback/details/382007/in-sqlcmd-scripts-setvar-should-have-a-lower-precedence-than-command-line-variable-assignments

Details (expand)

English

Tools (SSMS, Agent, Profiler, Migration, etc.)

Enhance the sqlcmd utility to support a new "sqlcmd Command" that supports in-script assignment of scripting variable at a precedence level lower than "System-level environmental variables," the current lowest level source for variable assignment.

Basically, it assigns a value to a variable only if the variable does not yet exist via some other source.

I imagine a script something like this (where :defaultvar is the proposed, hypothetical new sqlcmd Command) ....
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
:defaultvar dbName=master
use [$(dbName)]
SELECT COUNT(*) FROM sys.objects;
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

then this command would always work:
sqlcmd -i "myscript.sql"

and this command would generate the expected results:
sqlcmd -i "myscript.sql" -v dbName=msdb

Improved Administration

faster development

0 attachments

Posted by Microsoft on 1/21/2013 at 9:19 AM

Thanks for your feedback. We will review the suggestion for the next release.

Microsoft