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

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

Solution 2

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.

Solution 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;

Comments

[Zanoni]

In this case, you need to do: SELECT YOURVALUE -- and not RETURN YOURVALUE

[George2]

"you need to do: SELECT YOURVALUE -- and not RETURN YOURVALUE" -- confused, what do you mean? Could you explain more please?

[George2]

@Andrew, I like your answer, but after reading from Marc's reply, I think the more elegant way should use return value of store procedure, any comments?

[Andrew Hare]

@George2: Both solutions will work just fine - you should choose the one that you prefer :)