CODEWITHSUNDEEP
c#sql-serverstored-procedures
Keith
Keith

Reputation: 179

SQL Server stored procedure for Counting returning 0 as the count

At the moment I would like to run a stored procedure that returns the count of the number of records. The records look like this

Records results

The stored procedure looks like 

ALTER PROCEDURE [dbo].[USP_NEWFOLDER_COUNT]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @@ROWCOUNT 
    FROM  FileLocation
END

The calling c# code is 

public String CountNewFolder()
{
    tempInt = 0;

    using (SqlConnection sqlCon = new SqlConnection(connString))
    {
        SqlCommand sqlComm = new SqlCommand("USP_NEWFOLDER_COUNT", sqlCon);
        sqlComm.CommandType = CommandType.StoredProcedure;

        sqlCon.Open();
        tempInt = Convert.ToInt16(sqlComm.ExecuteScalar());
        sqlCon.Close();
    }

    return tempInt.ToString();
}

The result of my query is 0;

Any help or steers would be helpful. But, if possible, could I get a quick explanation so that I won't make the same mistake again.

Upvotes: 2

Views: 2688

Answers (3)

M.Ali
M.Ali

Reputation: 69524

Ideally you should use OUTPUT parameters for this kind of operation. Something like....

ALTER PROCEDURE [dbo].[USP_NEWFOLDER_COUNT]
 @Count INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Count = COUNT(*)
    FROM  FileLocation;
END

Your C# code would look something like... 

public String CountNewFolder()
{
    tempInt = 0;

    using (SqlConnection sqlCon = new SqlConnection(connString))
    {
      using(SqlCommand sqlComm = new SqlCommand("USP_NEWFOLDER_COUNT", sqlCon))
      {
        sqlComm.CommandType = CommandType.StoredProcedure;

        sqlComm.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;

        sqlCon.Open();
        sqlComm.ExecuteNonQuery();

        tempInt = Convert.ToInt32(sqlComm.Parameters["@Count"].Value);
      }
    }

    return tempInt.ToString();
}

Upvotes: 2

Crowcoder
Crowcoder

Reputation: 11514

You have the wrong idea of what @@ROWCOUNT is. That will return the number of rows acted upon by the last query. You have no queries so row count is zero. You want:

SELECT COUNT(*) FROM [FileLocation];

Upvotes: 4

marc_s
marc_s

Reputation: 754518

You're using @@ROWCOUNT which is not returning the number of rows in a table....

Use this instead:

ALTER PROCEDURE [dbo].[USP_NEWFOLDER_COUNT]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT COUNT(*)
    FROM  FileLocation
END

@@ROWCOUNT is a variable that gets filled after each SQL statement to show how many rows were affected by that last executed SQL statement. So if you would run a SELECT * FROM FileLocation, then after this command has run, then @@ROWCOUNT will contain the number of rows returned by that SELECT statement (but that would be less efficient than doing a SELECT COUNT(*) FROM FileLocation in the first place).

Upvotes: 4

Related Questions