Skip to content

Instantly share code, notes, and snippets.

@MikeFal
Created November 23, 2016 17:40
Show Gist options
  • Save MikeFal/2dec9dbed1903c6d7d621ca4c2213f18 to your computer and use it in GitHub Desktop.
Save MikeFal/2dec9dbed1903c6d7d621ca4c2213f18 to your computer and use it in GitHub Desktop.
#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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment