Geek Answers Handbook

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

If in the stored procedure I just execute one statement, select count(*) from sometableand then on the client side (I use C # ADO.Net SqlCommand to call the stored procedure), how can I get the value count(*)? I am using SQL Server 2008.

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

thanks in advance George

+5
source share
3 answers

Either you use ExecuteScalar, as Andrew suggested, or you have to change the code a 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();
}

Mark

PS: , ExecuteScalar . , (, ).

+6

ExecuteScalar - .

, . .

, count. int.

+6

marc_s . varchar .

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

More articles:


All Articles