Passing Variables to Sql Server Script

Here is something I found out today that might be useful for Sql Server users out there:

If you need to pass a variable to a sql script file in runtime, you can use the sqlcmd tool with the -v option, for example:

[code:c#]

sqlcmd -S localhost\SQLEXPRESS -i myscript.sql -v myVar1=“myVar1Value” myVar2=“myVar2Value”

[/code]

Then, inside the sql script, those variables can be accessed using the $(var) format:

[code:c#]

ALTER TABLE [dbo].[$(myVar1)] INSERT … VALUES ($(myVar2), $(myVar3) …)

[/code]

You can also specify these vars inside the sql script with setvar. More information in this MSDN article (Using sqlcmd with Scripting Variables).