Capture Record Count of result set

1

I run a SQL statement, and IF @@ROWCOUNT > 0 then I run a PRINT statement for my own debugging purposes as follows:
IF @@ROWCOUNT > 0
PRINT 'We found ? records without using PartNumber as a parameter.'
I want to print out also in the above print statement the number of rows in the result set, but how?

I tried RTRIM(LTRIM(@@ROWCOUNT)) but it appears that by running IF @@ROWCOUNT that it resets @@ROWCOUNT to 0. So, how do you get the number of rows?

2 
DECLARE @rowCount int = 0;

{your code here}

SET @rowCount = @@ROWCOUNT;

IF @rowCount > 0
BEGIN
PRINT ...
END

Instead of using print - you can use RAISERROR - with error code and statuses that won't stop your process (unless you want it to stop. Example:

RAISERROR('We found %i records without using PartNumber as a parameter', -1, -1, @rowCount) WITH nowait;

3

Thank you so much, and also for the RAISERROR information,. I am sort of new to T-SQL. Although I've had classes and done a lot of writign code in SQL, it was within Visual FoxPro and therefore not true T-SQL. I am running into some T-SQL roadblocks due to this deficiency in my experience. Thank you again, Jeff.

4

I don't find PRINT very helpful for debugging - its too fragile if concatenating values to make the single string it needs and one of them is the wrong type, or NULL (in which case the whole string becomes NULL unless you take evasive action with COALESCE(,,,) or similar.

Dunno if what we do would suit you? all our Stored Procedures take a final parameter of @intDebug and if that is set to 1 then a SELECT statement is used to display debug data.

SELECT Col1, Col2, ...
FROM MyTable
WHERE ColX = @MyParameter
ORDER BY ...
SELECT @intRowCount = @@ROWCOUNT, @intErrNo = @@ERROR

IF @intDebug = 1 SELECT [MySProcName]='DEBUG(1)',
                        [@intRowCount]=@intRowCount,
                        [@intErrNo]=@intErrNo,
                        [@MyParameter]=@MyParameter,
                        ...

The DEBUG(1) is intended to be a marker so that the location in the code, of the Debug Statement, can easily be found. We aim to use unique IDs in each DEBUG(nnn) message, but obviously its not critical