Store Count as a variable

Hi.

You can look at @@ROWCOUNT http://technet.microsoft.com/en-us/library/ms187316.aspx and see if it is helpful to you in your script.  

One simple way:

If exists (select somecolumn from sometable) begin
--1+ records
End
Else begin
--0 records
End

I agree with Patrick.  If you don't really need to store the row count, then I would just use EXISTS as shown.  If you need to do a selection, then do additional work based on the number of rows that is where @@ROWCOUNT would come in handy.

EXISTS method is much more efficient, especially if you don't actually care what the count is -- just that it is more than 0.

Even in an a OPENQUERY?

Please explain, what is it you are trying to do exactly.  A post of the current query would be helpful also and point out where you need the conditional based on number of rows.  

If there any records then I want it to Email the subscribed users

Else do nothing until the next scheduled time to run

SELECT iruser 'User',
	COUNT(*) '# of RA Lines'

FROM OPENQUERY (MyData, '
SELECT *
FROM itemrech
WHERE loc = 1
	AND irsrc = ''R''
	AND irdate = (CURRENT_DATE - 1 DAYS)	
')
GROUP BY iruser

Select allOpen in new window

If you are trying to do an update or something, then you would just add the EXISTS in WHERE condition by the way.

i.e.,
UPDATE your_table
...
WHERE EXISTS (SELECT 1 FROM some_other_table WHERE some_other_table.column = your_table.column)

I have also done something like this with UNION if you are needing to do a different selection based on non-existence of a primary query:

SELECT ...
FROM your_table_1
UNION
SELECT ...
FROM your_table_2
WHERE NOT EXISTS (SELECT 1 FROM your_table_1)

The second bit only fires if the first doesn't yield results ... caveats of column count, data type, etc. as with any UNION query but under right circumstances can be very effective.

If you have some other purpose in OPENQUERY, please expand on what it is you are looking for.

Thanks!

I basically just have a SSRS scheduled to run and if there is no records I do not want it to Email the users with a blank Email.

So @@ROWCOUNT is a system variable that automatically stores the ROW Count?

Yes, you got it.  See the technet article for more details like when to use ROWCOUNT_BIG.