title: Calling stored procedure with return value
body: <p>I am trying to call a stored procedure from my C# windows application. The stored procedure is running on a local instance of SQL Server 2008. I am able to call the stored procedure but I am not able to retrieve the value back from the stored procedure. This stored procedure is supposed to return the next number in the sequence. I have done research online and all the sites I've seen have pointed to this solution working.</p>
  
  <p>Stored procedure code:</p>
  
  <pre><code>ALTER procedure [dbo].[usp_GetNewSeqVal]
        @SeqName nvarchar(255)
  as
  begin
        declare @NewSeqVal int
        set NOCOUNT ON
        update AllSequences
        set @NewSeqVal = CurrVal = CurrVal+Incr
        where SeqName = @SeqName
  
        if @@rowcount = 0 begin
  print 'Sequence does not exist'
              return
        end
  
        return @NewSeqVal
  end
  </code></pre>
  
  <p>Code calling the stored procedure:</p>
  
  <pre><code>SqlConnection conn = new SqlConnection(getConnectionString());
  conn.Open();
  
  SqlCommand cmd = new SqlCommand(parameterStatement.getQuery(), conn);
  cmd.CommandType = CommandType.StoredProcedure;
  
  SqlParameter param = new SqlParameter();
  
  param = cmd.Parameters.Add("@SeqName", SqlDbType.NVarChar);
  param.Direction = ParameterDirection.Input;
  param.Value = "SeqName";
  
  SqlDataReader reader = cmd.ExecuteReader();
  </code></pre>
  
  <p>I have also tried using a <code>DataSet</code> to retrieve the return value with the same result. What am I missing to get 
  the return value from my stored procedure? If more information is needed, please let me know.</p>