SQLPS – Pass Parameter into SQL – invoke-Sqlcmd vs. invoke-DbaQuery

Posted on

Question :

OK, have a little bit of a puzzle. If I use invoke-sqlcmd by itself with -Variable I can successfully pass a variable into my .sql script

Now I need to do this against multiple instances, and thought I could use Get-DbaRegisteredServer (to use my CMS servers). However, it appears that Get-DbaRegisteredServer doesn’t work with invoke-sqlcmd, SOO, I decided to try invoke-dbaquery which does work.

However, I would need the equivalent of -Variable, and it seems that -SqlParameters would do the trick, but not sure how to use this.

where with invoke-sqlcmd -variable myvar=”myval”, but need help to get the same behavior with invoke-dbaquery. What am I missing? thanks in advance!

Answer :

Invoke-DbaQuery‘s -SqlParameters parameter requires a hash object and a parameterized query. So if your query is this:

$YourQuery = "select field1, field2 from table where field3=@MyVar;";

You will need this:

$Params = @{
  MyVar='Value Goes here';
Invoke-DbaQuery -query $YourQuery -SqlParameters @Params -SqlInstance INSTANCELIST;

You can specify the hash table inline as well but my personal preference is to make it separate, as this makes it easier to change the values based on conditionals.

Leave a Reply

Your email address will not be published. Required fields are marked *