CODEWITHSUNDEEP
powershellpowershell-4.0
Elisabeth
Elisabeth

Reputation: 21206

Concat invoke-SqlCmd query string does not work

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.

Upvotes: 2

Views: 761

Answers (2)

Matt
Matt

Reputation: 46710

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

Upvotes: 1

Jower
Jower

Reputation: 575

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.

Upvotes: 0

Related Questions