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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -