How to count records in a sproc 3

[LonnieJohnson]

I have a stored procedure that runs a multi join select and returns a set of records.

I have a SQL Server agent job that I want to run if there are records returned. How do I tell if there are records from my stored procedure?

Thanks.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[sblocher]

@@rowcount returns the number of rows retrieved by a SELECT, or updated by an UPDATE, etc. So, for example:

SELECT .... FROM ...
if @@rowcount > 0
BEGIN
-- Do whatever you want here
END

Be careful, @@rowcount is only good for the previous statement, so if you need other statements before checking @@rowcount, save it into a local variable immediately after the SELECT.

 

[bborissov]

Faster:

IF EXISTS(put your select here)
   BEGIN
       -- Do your job
   END

EXISTS() function just check if there is a records returned from your query, if there is at least one record it returns true and stop executing further. That way is faster than run the query and then check if there are any records in the result set.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[LonnieJohnson]

Both answers were good. The first one is what I asked for. But the second one gave me what I needed more efficiently. Thank you both. I have two new ways to look at things.

A STAR for each of you.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!