Rows affected number | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Rows affected number

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.
Make use of @@RowCount variable immediately after the statement Madhivanan Failing to plan is Planning to fail
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)
SET @RowsAffected = @@RowCount
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.
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 @st[email protected];
——————— Any ideas? Will that work good enough?