How to retrieve scalar value from stored procedure (ADO.NET)

Question

If in the stored procedure, I just execute one statement, select count(*) from sometable, then from client side (I am using C# ADO.Net SqlCommand to invoke the stored procedure), how could I retrieve the count(*) value? I am using SQL Server 2008.

I am confused because count(*) is not used as a return value parameter of stored procedure.

thanks in advance, George

Answer 1

Either you use ExecuteScalar as Andrew suggested - or you'll have to change your code a little bit:

CREATE PROCEDURE dbo.CountRowsInTable(@RowCount INT OUTPUT)
AS BEGIN
  SELECT
    @RowCount = COUNT(*)
  FROM 
    SomeTable
END

and then use this ADO.NET call to retrieve the value:

using(SqlCommand cmdGetCount = new SqlCommand("dbo.CountRowsInTable", sqlConnection))
{
  cmdGetCount.CommandType = CommandType.StoredProcedure;

  cmdGetCount.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;

  sqlConnection.Open();

  cmdGetCount.ExecuteNonQuery();

  int rowCount = Convert.ToInt32(cmdGetCount.Parameters["@RowCount"].Value);

  sqlConnection.Close();
}

Marc

PS: but in this concrete example, I guess the alternative with just executing ExecuteScalar is simpler and easier to understand. This method might work OK, if you need to return more than a single value (e.g. counts from several tables or such).