PowerShell: Write to SQL using stored procedure
# Create SqlConnection object
try {
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server={ServerNameOrInstance};Database={DatabaseName};Trusted_Connection=True;"
$SqlConnection.Open()
} catch {
# Catch any errors
}
if ($SqlConnection.State -eq [System.Data.ConnectionState]::Open) {
# Create SqlCommand object
$SqlCommand = $SqlConnection.CreateCommand() # returns System.Data.SqlClient.SqlCommand
$SqlCommand.CommandTimeOut = 30
$SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCommand.CommandText = "my_stored_proc_name"
# Add parameters to pass values to the stored procedure
$SqlCommand.Parameters.AddWithValue("@Field1", $Field1) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Field2", $Field2) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Field3", $Field3) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Field4", $Field4) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Field5", $Field5) | Out-Null
# Prepare for a Return value (without defining, cannot get return value)
$ReturnValueParam = $SqlCommand.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]::Int)
$ReturnValueParam.Direction = [System.Data.ParameterDirection]::ReturnValue
# Execute a non-query
$SqlCommand.ExecuteNonQuery() | Out-Null
# Get the return value if query executed successfully
$ReturnValue = [int]$ReturnValueParam.Value
}