SQLTeam.com | Weblogs | Forums

Cannot seem to capture row count


#1

I tried to capture the numbers of records for a statement but
nothing shows up. I was using the debugger (F11).

DECLARE @nRow_Count INT
SQL Statement runs here: IF EXISTS(…)
BEGIN
@nRow_Count = @@ROWCOUNT
PRINT LTRIM(RTRIM(@nRow_Count))) + ‘ records found.’
END

Why does this not work?


#2

Have you tried without the BEGIN/END?


#3

What are you trying to count? First, if there are no rows returned from the query within the IF EXISTS block, nothing will be printed. See this example:

DECLARE @nRow_Count INT
IF EXISTS( SELECT TOP (0) * FROM master.dbo.spt_values )
BEGIN
	SET @nRow_Count = @@ROWCOUNT
	PRINT LTRIM(RTRIM(@nRow_Count))+ ' records found'
END

Second, even if there are records returned in from the select within the IF EXISTS clause, those rows are not captured by the @@ROW_COUNT function. See this example:

DECLARE @nRow_Count INT
IF EXISTS( SELECT * FROM master.dbo.spt_values )
BEGIN
	SET @nRow_Count = @@ROWCOUNT
	PRINT LTRIM(RTRIM(@nRow_Count))+ ' records found'
END

If you do want to print the number of rows, do something like this:

DECLARE @nRow_Count INT
IF EXISTS( SELECT * FROM master.dbo.spt_values )
BEGIN
	SELECT TOP 10 * FROM sys.all_columns
	SET @nRow_Count = @@ROWCOUNT
	PRINT LTRIM(RTRIM(@nRow_Count))+ ' records found'
END

or perhaps this:

DECLARE @nRow_Count INT;

SELECT @nRow_Count = COUNT(*) FROM sys.all_columns
PRINT LTRIM(RTRIM(@nRow_Count))+ ' records found'

#4

Just an observation, but this seems a heavy duty method of converting Integer to String, but maybe its efficient? although both left and Right trimming aren't required ...