How to Get a ResultSet Count

Question

Post reply

How to Get a ResultSet Count

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
October 24, 2009 at 4:26 am

#73001

Dear All;
Pls Help this;
How to get the ResultSet Count IN SQLSERVER 2005

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Look at @@ROWCOUNT

For example:

UPDATE authors SET au_lname = 'Jones'

WHERE au_id = '999-888-7777'

IF @@ROWCOUNT = 0

print 'Warning: No rows were updated'

If you are using SQL 2005 then read in Books On Line at:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dmo9/html/4e84f224-01de-4e93-82ee-73011ec32470.htm

If not please post your code so someone can assist you further.

Note that you have posted your question to a SQL Server 2000 forum, but are asking about SQL Server 2005 .... which is it?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 2

Dear All;

I have To Run The Sp;

Like this (Stored Procedure) Get_Name 'Usa'

the following Results are Shown

Id name

1 Sam

2 Vino

3 Rose

I need the Row count for Value = 3

How To I Get the Row Count;

pls Help This

Thanks & Advance;

A.Faijurrahuman

Coimbatore--

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi,

The same method mentioned by bit bucket

Exec Get_Name 'Usa'

Select @@rowcount

Ensure that, your procedure should not having/use of SET NOCOUNT ON/OFF.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

arun.sas (10/26/2009)
Exec Get_Name 'Usa'
Select @@rowcount
Ensure that, your procedure should not having/use of SET NOCOUNT ON/OFF.

The setting of NoCount has no effect on @@RowCount. Setting NoCount on simply prevents SQL from returning the "x row(s) affected" message to the client.

CREATE PROCEDURE TestRowCount AS

SET NOCOUNT ON

SELECT * FROm sys.objects

GO

EXEC TestRowCount

SELECT @@RowCount

GO

DROP PROCEDURE TestRowCount

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arun.sas SSChampion Points: 11831 More actions · Answer 5

Hi Gail,

Thanks, but the results vary by set nocount on/off

CREATE PROCEDURE TestRowCount AS

begin

SELECT * FROm sysobjects

end

GO

EXEC TestRowCount

SELECT @@RowCount

RESULT

some rows and

row count = 2170

alter PROCEDURE TestRowCount AS

begin

set nocount on

SELECT * FROm sysobjects

set nocount off

end

GO

EXEC TestRowCount

SELECT @@RowCount

RESULT

some rows and

row count = 0

Is anything I miss to obtain the result?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

arun.sas (10/26/2009)
Thanks, but the results vary by set nocount on/off

Nope, it's not the setting of NoCount that's the cause.

Is anything I miss to obtain the result?

Yes. Common mistake. It's not the fact that nocount is set to on that caused @@RowCount to be 0, it's the fact that there was another statement after the select.

@@RowCount returns the row count of the last statement to be executed. The last statement that ran before you captured the rowcount was SET NOCOUNT OFF. That will obviously affect 0 rows.

See my example, it returns the row count correctly, even though ROWCOUNT is on. To see how this behaves, look at these.

CREATE PROCEDURE TestRowCount AS

SET NOCOUNT ON

SELECT * FROm sysobjects

GO

EXEC TestRowCount

SELECT @@RowCount -- returns 58, even though NOCOUNT is on (I'm running this in TempDB)

GO

ALTER PROCEDURE TestRowCount AS

SET NOCOUNT OFF

SELECT * FROm sysobjects

Print 'All Done'

GO

EXEC TestRowCount

SELECT @@RowCount -- returns 0. Print does not affect any rows. NB, NOCOUNT is OFF.

GO

ALTER PROCEDURE TestRowCount AS

SET NOCOUNT OFF

SELECT * FROM sysobjects

SET DATEFORMAT ymd;

GO

EXEC TestRowCount

SELECT @@RowCount -- returns 0. The SET DATEFORMAT does not affect any rows. NB, NOCOUNT is OFF.

-- Cleanup

DROP PROCEDURE TestRowCount

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arun.sas SSChampion Points: 11831 More actions · Answer 7

GilaMonster (10/26/2009)
@@RowCount returns the row count of the last statement to be executed. The last statement that ran before you captured the rowcount was SET NOCOUNT OFF. That will obviously affect 0 rows.

Thanks Gail, Got the core.