title: Stored procedure returns negative value
body: <p>I'm working with PowerShell code which in call a procedure on database side. The stored procedure returns -1 and I know it's returned by ExecuteNonQuery(). Here is the code (I run it on azure under runbooks,there is no problem with connection) :</p>
  <pre><code>try {
      $database = 'database1'
      #Write-Output &quot;Starting ....&quot;
      # Create connection to Master DB
      Write-Output &quot;Running on &quot; $database
  
      $MasterDatabaseConnection =  New-Object System.Data.SqlClient.SqlConnection
      $MasterDatabaseConnection.ConnectionString = &quot;Data Source=$dataSource;Initial Catalog=$database;Integrated Security=False;User ID=$userNameSecret ;Password=$passwordSecret ;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False&quot;
      $MasterDatabaseConnection.Open()
  
      Write-Output $MasterDatabaseConnection.ConnectionString
      Write-Output &quot;Connection is opened...&quot;
  
      # Create command
      $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
      $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
      $MasterDatabaseCommand.CommandText = &quot;EXEC [dbo].[truncate_some_tables]&quot;
  
      Write-Output &quot;COMMAND = &quot; $SQLStatement
      Write-Output &quot;DATABASE COMMAND EXEC TRUNCATE_SOME_TABLES &quot;
  
      # Execute the query   
      $MasterDatabaseCommand.ExecuteNonQuery()
      Write-Output &quot;EXECUTING QUERY&quot;
      
  } catch {     
      Write-Output -Verbose &quot;Error executing SQL on database [$database] on server [$dataSource]. Statement: `r`n$SQLStatement&quot;
      throw $_.Exception
      Write-Output $_.Exception
  } finally {
      # Close connections to DB
      $MasterDatabaseConnection.Close()
      Write-Output &quot;CONNECTION CLOSED&quot;    
  }
  </code></pre>
  <p>And here is the stored procedure:</p>
  <pre><code>SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[truncate_some_tables]
  AS
  BEGIN
      TRUNCATE TABLE [internal].[operation_messages_scaleout];
      TRUNCATE TABLE [internal].[event_message_context_scaleout];
      TRUNCATE TABLE [internal].[event_messages_scaleout];
  END
  </code></pre>
  <p>Three tables(above) has data and are not empty.</p>
  <ul>
  <li>What should I add to my code to get more information about the error?</li>
  <li>Why it returns -1?</li>
  <li>How can I improve my code?</li>
  </ul>
  <p>Add some more information:</p>
  <p><a href="https://i.stack.imgur.com/VeJ8H.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VeJ8H.png" alt="enter image description here" /></a></p>