SQL Server Performance

Rows affected number

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by abradley81, Jul 9, 2006.

  1. abradley81 New Member

    I have two statements going, first one is a CTE and the second is a select statement which narrows down the results to only show 10 rows.

    So when I execute a stored procedure in Visual Basic, in the output window it says:

    (21 rows affected)
    (10 rows returned)

    I'm able to get the number of rows returned, but whats the easiest way to get that "rows affected" number from the stored procedure. I need an easy and quick way to pass back the "21" from my database.

    Thanks.
  2. Madhivanan Moderator

    Make use of @@RowCount variable immediately after the statement

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    Not familiar with SQL 2005, but what does @@ROWCOUNT say after you initialize the CTE? Or perhaps there is a CTE-specific function that you can call, similar to @@ROWCOUNT?

    Anyhow, you can use an output parameter in the stored procedure, like so

    CREATE PROCEDURE dbo.Test1 (@RowsAffected INT OUTPUT)
    AS
    SET NOCOUNT ON
    SELECT * FROM Table1
    SET @RowsAffected = @@RowCount
    SELECT * FROM Table2
    GO

    Call that procedure like this:

    DECLARE @HowMany INT
    EXEC dbo.Test1 @HowMany OUTPUT

    ... and after this your @HowMany variable holds the rowcount from the first select in the procedure.

    By the way, SET NOCOUNT ON is considered a good standard for stored procedures. Adding this option means you won't get the "rows affected" and "rows returned" messages printed, but then you can still use @@ROWCOUNT to get those numbers.
  4. abradley81 New Member

    Ok, I wasn't able to get @@rowcount to work inside or outside the CTE. Tried it a million and one ways and I don't think its a go.

    However, this is really all I can come up with to get a count of the original CTE select statement (without using a GROUP BY or a #temporary table).

    I added a SELECT statement which does the counting of the CTE, and return it to every row (which will only be 10 or less). Its not the best way, but seems to be the only way I can get a count of the CTE before it gets chopped down to 10.
    ---------------------
    WITH TempListings AS (
    SELECT TOP(200) ListingID,ROW_NUMBER() OVER (ORDER BY CurrentPrice) as Row
    FROM Listings
    )

    SELECT Listings.ListingID, CurrentPrice, SubString(Description, 0, 85) AS Description, PropTypeTitle, (SELECT COUNT(*) FROM TempListings) As TotalCount
    FROM Listings
    INNER JOIN TempListings ON TempListings.ListingID = Listings.ListingID
    INNER JOIN PropTypes ON Listings.PropType = PropTypes.PropType
    WHERE Row between @startRowIndex and @startRowIndex+@NumRows-1;
    ---------------------

    Any ideas? Will that work good enough?

Share This Page