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

Question

Hello everyone,

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

When you execute the query call ExecuteScalar - this will return the result.

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Since you are only returning one value this would return just the value from your count expression. You will need to cast the result of this method to an int.

Answer 2

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

Answer 3

marc_s answer worked fine for integer. but for varchar the lenght must be specifed.

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