title: Return values from SQL Server stored procedure
body: <p>I have a stored procedure in SQL Server that returns two values. When called from QA with the following SQL code:</p>
  
  <pre><code>exec TWEEPush_ValidateCO @CoFilter='CO IN(''1502'',''Mike'',''Clarkson'')', @TDate='09/18/`2017'
  </code></pre>
  
  <p>it works as expected by returning two values:</p>
  
  <pre><code>@TempCOID   @TempDate  
  1502        09/10/2017
  </code></pre>
  
  <p>The stored procedure does some work and ends with the two values being set to non null values, with the stored procedure ending with:</p>
  
  <pre><code>SELECT 
      @TempCOID AS N'@TempCOID',  
      @TempDate AS N'@TempDate'
  </code></pre>
  
  <p>In VB.net (Visual Basic, not C), I've tried many different approaches to get the results but have had no luck. Either I get an error saying the named param doesn't exist, or it ends up returning empty values (everything is coded to prevent Nulls in the return data)</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim sConnectStr As String = GetSQLConnectionString()
  
  Try
      Using Connection As New SqlConnection(sConnectStr)
          Connection.Open()
  
          Dim Command As New SqlCommand("dbo.TWEEPush_ValidateCO", Connection)
          Command.CommandType = CommandType.StoredProcedure
          Command.Parameters.AddWithValue("@CoFilter", Filter)
          Command.Parameters.AddWithValue("@TDate", Now())
  
          Dim PramCOIDRet As New SqlParameter
          PramCOIDRet.ParameterName = "@TempCOID"
          PramCOIDRet.SqlDbType = SqlDbType.NVarChar
          PramCOIDRet.Size = 30
          PramCOIDRet.Direction = ParameterDirection.Output
  
          Dim PramDateRet As New SqlParameter
          PramDateRet.ParameterName = "@TempDate"
          PramDateRet.SqlDbType = SqlDbType.NVarChar
          PramDateRet.Size = 30
          PramDateRet.Direction = ParameterDirection.Output
  
          Command.Parameters.Add(PramCOIDRet)
          Command.Parameters.Add(PramDateRet)
  
          Command.ExecuteNonQuery()
  
          Dim COID as string = Command.Parameters("@TempCOID").Value
          Dim CoDate as Date = CDate(Command.Parameters("@TempDate").Value)
      End Using
  Catch ex As Exception
  End Try
  </code></pre>
  
  <p>No matter what code I've tried, the code never returns the data as expected.
  What am I doing wrong? Can someone please shed some light on this please?</p>