ScaryDBA_TestScript.ps1

#Import Module (Requires SSMS 16, July 2016)
Import-Module SqlServer

$ConnectionString = 'Server=SHION;Database=WideWorldImporters;trusted_connection=true'
$BillToCustomerList = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query 'SELECT  DISTINCT i.BillToCustomerID FROM Sales.Invoices as i;'

#Create Temp Testing Stored Procedure 
$TesttingSproc = "IF (SELECT object_ID('temp_TestQuery')) IS NOT NULL
DROP PROCEDURE temp_TestQuery;
GO

CREATE PROCEDURE temp_TestQuery @BillToCustomerID INT
AS
SELECT si.StockItemName,
   i.InvoiceDate,
   i.SalespersonPersonID
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
   ON il.InvoiceID = i.InvoiceID
JOIN Warehouse.StockItems AS si
   ON si.StockItemID = il.StockItemID
WHERE i.BillToCustomerID = @BillToCustomerID;
GO"

Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $TesttingSproc

#Loop through and execute
foreach($row in $BillToCustomerList.BillToCustomerID)
{
    Invoke-Sqlcmd -ConnectionString $ConnectionString -Query "EXEC temp_testQuery @BillToCustomerID = $row;" | Out-Null     
}