How to Execute a Stored Procedure in MS SQL Using PowerShell

This article has not been completed yet. However, it may already contain helpful information and therefore it has been published at this stage.

Generating some Template Tables.....

CREATE TABLE Product
(ProductID INT, ProductName VARCHAR(100) )
GO
 
CREATE TABLE ProductDescription
(ProductID INT, ProductDescription VARCHAR(800) )
GO
 
INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58')
,(706,'HL Road Frame - Red, 58')
,(707,'Sport-100 Helmet, Red')
GO
 
INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel for entry-level rider.')
,(706,'Sturdy alloy features a quick-release hub.')
,(707,'Aerodynamic rims for smooth riding.')
GO

Checking Result....

/****** Skript für SelectTopNRows-Befehl aus SSMS ******/
SELECT TOP (1000) [ProductID]
      ,[ProductName]
  FROM [Test].[dbo].[Product]

Creating A Simple Stored Procedure.....

CREATE PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

Executing the Stored Procedure....

USE [Test]
GO

DECLARE @RC int

-- TODO: Legen Sie hier Parameterwerte fest.

EXECUTE @RC = [dbo].[GetProductDesc] 
GO

# Prequesites:

# dbatools - Modul & SqlServer:

# Install-Module -Name dbatools
# Install-Module -Name SqlServer

    # Modulecheck and Paths:
        # Get-Module
        #(gmo -l dbatools).path
        #(gmo -l SqlServer).path

# Zu Modifizieren:
# ---------------------------------------------------------------------------
# Variablen
$winCred = Get-Credential "TOMHOMEDESK\LokalAdmin"
# $sqlCred = Get-Credential sa
$DBHostname = "TOMHOMEDESK"
$SqlInstance = Find-DbaInstance -ComputerName $DBHostname
Test-DbaConnection -SqlInstance $SqlInstance -Credential $winCred # -Verbose #-SqlCredential $sqlCred
# $Database = "Test"
$Database = ((Get-DbaDatabase -SqlInstance $SqlInstance -ExcludeDatabase model,master,msdb,tempdb) | Select-Object Name | Out-GridView -Title "Select a Database" -PassThru).Name
$StoredProcedured = ((Get-DbaDbStoredProcedure -SqlInstance $SqlInstance -Database $Database -ExcludeSystemSp) | Select-Object Name | Out-GridView -Title "Select a Stored Procedured" -PassThru).Name
$results = Invoke-Sqlcmd -ServerInstance $SqlInstance -Database $Database -Query "$StoredProcedured" -Credential $winCred
$results

Sources:

Link: SQLShack - SQL Server stored procedures for beginners