How can I concat a list of parameter OR a string of parameter (better) to my sql query? The below does not work.
$parameters = @("-ServerInstance `"MyMachine\SQLEXPRESS`"", "-Database %TargetDbName%", "-Username %SQLUserName%", "-Password %SQLPassword%")
$row = Invoke-Sqlcmd -Query "SELECT field FROM Table;" $parameters
I want to execute later multiple queries all with the same connection parameters and it is usefull to reuse them in a string which I can just add to the query string.
You were on the right track. Sounds like you are looking for splatting.
Splatting is a method of passing a collection of parameter values to a command as unit.
I don't use Invoke-SQLcmd but it should work just like this:
$parameters = @{
ServerInstance = "MyMachine\SQLEXPRESS"
Database = "TargetDbName"
Username = "SQLUserName"
Password = "SQLPassword"
Query = "SELECT field FROM Table;"
}
$row = Invoke-Sqlcmd @parameters
Collect all the parameters as a hashtable and splat the cmdlet. If you wanted to use this parameter set again later, but make small changes, that would be easy now by referencing the name/value pair of the hashtable.
$parameters.Query = "SELECT field FROM DifferentTable;"
$anotherRow = Invoke-Sqlcmd @parameters
Have a look at parameter splatting
This means that you can put the arguments into a hashtable and pass the hashtable as parameters. Given your code, you could change it to. Notice that even though i assign the parameters to a hashtable $parameters you have to send it to the cmdlet using the @parameter syntax.
$parameters = @{ServerInstance="MyMachine\SQLEXPRESS";Database="$env:TargetDbName";Username="$env:SQLUserName";Password="$env:SQLPassword"}
$row = Invoke-Sqlcmd -Query "SELECT field FROM Table;" @parameters
I assumed that the TargetDBName, Username and password were to be found in environment variables so i changed the code a little to get those as well.
Give it a go.