Best Practice to mock cmdlet that gets called multiple times in loop

Pester
1

Hi,

What is the best method to mock a cmdlet that is expected to execute multiple times in a foreach loop and each time the mocked cmdlet is expected to produce different output and that output is collected within an array?

Is the Mock-Command written out multiple times within your pester test?

Within my code the Invoke-SQL command will be executed 3 times to pull back different data from a SQL table. And the result set of each invoke is collected within an array variable. I am puzzled on how to provide test data and proper generate a pester test for this part of the function.

# snippet from my function 
$Queries = @{
    Query = "Select Customer From Customertable"
},
@{
    Query = "Select State From Customertable"
},
@{
    Query = "Select City From Customertable"
}

$result = @()
foreach ($q in $Queries) {
    $QueryResult = Invoke-SQL -Query $q -ConnString $ConnString
    $result += $QueryResult
}

#...then I do some validation on the data that is in the $result variable.
2

I figured out 1 method. Use -ParameterFilter with my Mock commmmand. I was able to control the output of the cmdlet being mocked using this method.