title: Calling a stored procedure in PowerShell to insert into SQL Server database
body: <p>I am attempting to call a stored procedure in a PowerShell script to insert data into a SQL Server table. The script runs fine with no errors, but the information that should be wrote to the table is not there. I'm not sure where the code doesn't work as i am fairly new to PowerShell. Any help with this would be appreciated.</p>
  
  <pre><code>function WriteData 
  {
      param
      (
          [String] $Server,
          [int] $CheckId,
          [String] $Investigate
      )
  
      try
      {
           $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
           $SqlConnection.ConnectionString = "Server=MyServer;DataBase=MyDatabase;Integrated Security=SSPI"
           $SqlConnection.open()
           $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
           $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
           $SqlCmd.CommandText = "EXEC dbo.usp_InsertTest"
  
           $param1=$sqlcmd.Parameters.Add("@param1" , [System.Data.SqlDbType]::VarChar)
           $param1.Value = $param1
           $param2=$sqlcmd.Parameters.Add("@param2" , [System.Data.SqlDbType]::Int)
           $param2.Value = $param2
           $param3=$sqlcmd.Parameters.Add("@param3" , [System.Data.SqlDbType]::VarChar)
           $param3.Value = $param3
  
           $SqlCmd.Connection = $SqlConnection
           $tmp=$SqlCmd.ExecuteNonQuery()
           $SqlConnection.Close()
       }
       catch
       {
           if( $SqlConnection.State -eq "Open" )
           {
               $SqlConnection.Close()
           }
           return -1
       }
   }
  
   WriteData -Param1 Test -Param2 1 -Param3 HelloWorld
  </code></pre>