Need @@RowCount from a select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need @@RowCount from a select query

Author

Topic

feejaz
Yak Posting Veteran

68 Posts

Posted - 2012-02-08 : 09:06:42

Hi everyone,

I have a select query and want to create a function that can return a @@RowCount. Below is the query;

SELECT StudentID, COUNT(*) TotalCount
FROM EventEnrollment
WHERE EventEnrollment.EnrollmentStatusCD='ENROLLED'

GROUP BY StudentID
HAVING COUNT(*) > 0

above query working fine and returns the 115 Rows and I want to get the @@RowCount return as integer.

Will anyone know how can I do this, I used the below query and it returns two table but I only need 2nd query's result.

SELECT StudentID, COUNT(*) TotalCount
FROM EventEnrollment
WHERE EventEnrollment.EnrollmentStatusCD='ENROLLED'

GROUP BY StudentID
HAVING COUNT(*) > 0

SELECT @@RowCount as Rows

It will be good if some one can create a sql function to return the @@RowCount. I tried but could not create SQL Function.

I am not able to create below function;

Create function [dbo].[f_testStudent]
(
)
RETURNS int
AS
BEGIN

DECLARE @Count int

SELECT StudentID, COUNT(*) TotalCount
FROM EventEnrollment
WHERE EventEnrollment.EnrollmentStatusCD='ENROLLED'

GROUP BY StudentID
HAVING COUNT(*) > 0

set @Count = @@RowCount
RETURN @Count

END

******************************

need help to create above function.

Regards,

Feejaz

Navi

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-08 : 09:39:57

How about just

SELECT COUNT(*) FROM (
SELECT StudentID, COUNT(*) TotalCount
FROM EventEnrollment
WHERE EventEnrollment.EnrollmentStatusCD='ENROLLED'

GROUP BY StudentID
HAVING COUNT(*) > 0) s

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-02-08 : 09:49:45

I have not clearly understood your question, this may help if not exaplain what clarification you need please


DECLARE @MyCount int
SELECT	StudentID, COUNT(*) TotalCount
FROM	EventEnrollment
WHERE	EventEnrollment.EnrollmentStatusCD='ENROLLED' 

GROUP BY StudentID
HAVING COUNT(*) > 0
SELECT @MyCount = @@ROWCOUNT

You now have a recordset which has been output (two columns, Student ID and the COUNT of their number of enrollments. You also have a count (stored in @MyCount variable) of the number of rows that were in that recordset. You can output that as a separate recordset (i.e. a second recordset):

SELECT @MyCount AS [Rows]

or you could return it:

RETURN @MyCount

If you do NOT want the first recordset AT ALL, but just want to know the number of rows in that recordset then:


SELECT COUNT(*) AS [Rows]
FROM
(
    SELECT	StudentID, COUNT(*) TotalCount
    FROM	EventEnrollment
    WHERE	EventEnrollment.EnrollmentStatusCD='ENROLLED' 
    
    GROUP BY StudentID
    HAVING COUNT(*) > 0
) AS X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources