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

ado.net, sql, sql-server-2008, stored-procedures

Solution

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).

Problem

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

Original source