Store Count as a variable

I want to be able to run my SELECT
have an indicator if there are zero or one or more records

so that way if records <1 nothing happens
ELSE
DO ....

jaymz69Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Didn't see your last post, sorry.

If I understand correctly, that is what I thought previously:

-- your select
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

-- notify based on @@ROWCOUNT
IF (@@ROWCOUNT > 0)
BEGIN
   -- send e-mail here
END
0
 
Kevin CrossChief Technology OfficerCommented:
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.  
0
 
Patrick MatthewsCommented:
One simple way:

If exists (select somecolumn from sometable) begin
--1+ records
End
Else begin
--0 records
End
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
jaymz69Author Commented:
Even in an a OPENQUERY?
0
 
Kevin CrossChief Technology OfficerCommented:
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.  
0
 
jaymz69Author Commented:
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

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
jaymz69Author Commented:
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.

0
 
jaymz69Author Commented:
So @@ROWCOUNT is a system variable that automatically stores the ROW Count?
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, you got it.  See the technet article for more details like when to use ROWCOUNT_BIG.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.