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

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!

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.