Number of records returned in a query 1

[wbodger]

If I have a simple query

select STUDY_CONSENT_DATE, ENTRYFLAG, STUDY_PROTOCOL_VISIT_CODE
from dbedrn316.dbo.tblBLConsent c join dbedrn316.dbo.tblPELEMENT pe
	on c.FID=pe.FORMID
where pe.DE_ID=1319
and STUDY_PARTICIPANT_ID=2065551212
and DELETEFLAG=0

and I want to have the number of records as part of the recordset before I loop thru all of the records, what do I do?

wb

 

[djj55]

How will you be "looping through"? The function @@ROWCOUNT returns the value but only after the query.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[denimined]

Try this...

select c.STUDY_CONSENT_DATE,
c.ENTRYFLAG,
c.STUDY_PROTOCOL_VISIT_CODE,
(Select COUNT(c.FID) as recs
from dbedrn316.dbo.tblBLConsent AS c
join dbedrn316.dbo.tblPELEMENT AS pe
on c.FID = pe.FORMID
where pe.DE_ID = 1319
and c.STUDY_PARTICIPANT_ID = 2065551212
and c.DELETEFLAG = 0
) AS recs
from dbedrn316.dbo.tblBLConsent AS c
join dbedrn316.dbo.tblPELEMENT AS pe
on c.FID = pe.FORMID
where pe.DE_ID = 1319
and c.STUDY_PARTICIPANT_ID = 2065551212
and c.DELETEFLAG = 0

 

[wbodger]

That is what I was looking for, thanks!